How to drop column with constraint?

by

First you should drop the problematic DEFAULT constraint, after that you can drop the column

alter table tbloffers drop constraint [ConstraintName]
go

alter table tbloffers drop column checkin

But the error may appear from other reasons – for example the user defined function or view with SCHEMABINDING option set for them.

UPD:
Completely automated dropping of constraints script:

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'alter table tbloffers drop constraint ['+dc.NAME+N']'
    from sys.default_constraints dc
    JOIN sys.columns c
        ON c.default_object_id = dc.object_id
    WHERE 
        dc.parent_object_id = OBJECT_ID('tbloffers')
    AND c.name = N'checkin'
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

More Related Contents:

  1. Export from SQL Server to Excel
  2. How to get cumulative sum
  3. How to check if a column exists in a SQL Server table
  4. SQL Server query to find all permissions/access for all users in a database
  5. Infinite loop CTE with OPTION (maxrecursion 0)
  6. How Can I Log and Find the Most Expensive Queries?
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. SQL Server add auto increment primary key to existing table
  9. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  10. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  11. INSERT INTO vs SELECT INTO
  12. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  13. Do while loop in SQL Server 2008
  14. Query times out when executed from web, but super-fast when executed from SSMS
  15. TSQL: How to convert local time to UTC? (SQL Server 2008)
  16. How to update primary key
  17. error when inserting into table having instead of trigger from entity data framework
  18. Case sensitive variables in SQL Server
  19. Transpose rows and columns with no aggregate
  20. SQL Server Configuration Manager cannot be found
  21. SQL Server recursive query
  22. How do I move a table into a schema in T-SQL
  23. nvarchar(max) vs NText
  24. How to display two digits after decimal point in SQL Server
  25. How to find the boundaries of groups of contiguous sequential numbers?
  26. “Prevent saving changes that require the table to be re-created” negative effects
  27. How to change column datatype in SQL Server database without losing data?
  28. Table Valued Function Killing My Query Performance
  29. How to convert text column to datetime in SQL
  30. How to choose the clustered index in SQL Server?

Leave a Comment