How to drop a column with constraints in SQL Server 2005

Here is a script that will delete the column along with its default constraint. Replace MYTABLENAME and MYCOLUMNNAME appropriately.

declare @constraint_name sysname, @sql nvarchar(max) select @constraint_name = name from sys.default_constraints where parent_object_id = object_id('MYTABLENAME') AND type = 'D' AND parent_column_id = ( select column_id from sys.columns where object_id = object_id('MYTABLENAME') and name = 'MYCOLUMNNAME' ) set @sql = N'alter table MYTABLENAME drop constraint ' + @constraint_name exec sp_executesql @sql alter table MYTABLENAME drop column MYCOLUMNNAME go 

Perhaps it could help a little more:

declare @tablename nvarchar(200) declare @colname nvarchar(200) declare @default sysname, @sql nvarchar(max) set @tablename = 'your table' set @colname = 'column to drop' select @default = name from sys.default_constraints where parent_object_id = object_id(@tablename) AND type = 'D' AND parent_column_id = ( select column_id from sys.columns where object_id = object_id(@tablename) and name = @colname ) set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default exec sp_executesql @sql set @sql = N'alter table ' + @tablename + ' drop column ' + @colname exec sp_executesql @sql 

Only need to set the @tablename & @colname variables to drop the column.

> select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE > WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed' 

It's not the right solution as it is explained here : http://msdn.microsoft.com/en-us/library/aa175912.aspx that :

Unfortunately, the name of the column default constraint isn't kept in the ANSI COLUMNS view, so you must go back to the system tables to find the name

The only way I found to get the name of the DEFAULT constraint is this request :

select t_obj.name as TABLE_NAME ,c_obj.name as CONSTRAINT_NAME ,col.name as COLUMN_NAME from sysobjects c_obj join sysobjects t_obj on c_obj.parent_obj = t_obj.id join sysconstraints con on c_obj.id = con.constid join syscolumns col on t_obj.id = col.id and con.colid = col.colid where c_obj.xtype = 'D' 

Am I the only one to find it crazy to be unable to delete easily a constraint that only concerns the columns I'm trying to drop ?
I need to execute a request with 3 joins just to get the name...

I also think it's a shortcoming in SQL server to not have a cascading drop available. I worked my way around it by querying the system tables in the same way as other people described here:

• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE only lists foreign keys, primary keys and unique constraints.
• The only way to look for default constraints is to look for them in sys.default_constraints.
• what hasn't been mentioned here yet, is that indexes also make dropping a column fail, so you also need to drop all indexes that use your column before you can proceed with dropping a column.

The resulting script is not pretty, but I put it in a stored procedure to be able to reuse it:

CREATE PROCEDURE DropColumnCascading @tablename nvarchar(500), @columnname nvarchar(500) AS SELECT CONSTRAINT_NAME, 'C' AS type INTO #dependencies FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname INSERT INTO #dependencies select d.name, 'C' from sys.default_constraints d join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id join sys.objects o ON o.object_id = d.parent_object_id WHERE o.name = @tablename AND c.name = @columnname INSERT INTO #dependencies SELECT i.name, 'I' FROM sys.indexes i JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id JOIN sys.objects o ON o.object_id = i.object_id where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0 DECLARE @dep_name nvarchar(500) DECLARE @type nchar(1) DECLARE dep_cursor CURSOR FOR SELECT * FROM #dependencies OPEN dep_cursor FETCH NEXT FROM dep_cursor INTO @dep_name, @type; DECLARE @sql nvarchar(max) WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = CASE @type WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']' WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']' END print @sql EXEC sp_executesql @sql FETCH NEXT FROM dep_cursor INTO @dep_name, @type; END DEALLOCATE dep_cursor DROP TABLE #dependencies SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']' print @sql EXEC sp_executesql @sql 

The answer from pvolders was just what I needed but it missed statistics which were causing and error. This is the same code, minus creating a stored procedure, plus enumerating statistics and dropping them. This is the best I could come up with so if there is a better way to determine what statistics need to be dropped please add.

DECLARE @tablename nvarchar(500), @columnname nvarchar(500) SELECT @tablename = 'tblProject', @columnname = 'CountyKey' SELECT CONSTRAINT_NAME, 'C' AS type INTO #dependencies FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname INSERT INTO #dependencies select d.name, 'C' from sys.default_constraints d join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id join sys.objects o ON o.object_id = d.parent_object_id WHERE o.name = @tablename AND c.name = @columnname INSERT INTO #dependencies SELECT i.name, 'I' FROM sys.indexes i JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id JOIN sys.objects o ON o.object_id = i.object_id where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0 INSERT INTO #dependencies SELECT s.NAME, 'S' FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns AS c ON sc.object_id = c.object_id AND c.column_id = sc.column_id WHERE s.object_id = OBJECT_ID(@tableName) AND c.NAME = @columnname AND s.NAME LIKE '_dta_stat%' DECLARE @dep_name nvarchar(500) DECLARE @type nchar(1) DECLARE dep_cursor CURSOR FOR SELECT * FROM #dependencies OPEN dep_cursor FETCH NEXT FROM dep_cursor INTO @dep_name, @type; DECLARE @sql nvarchar(max) WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = CASE @type WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']' WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']' WHEN 'S' THEN 'DROP STATISTICS [' + @tablename + '].[' + @dep_name + ']' END print @sql EXEC sp_executesql @sql FETCH NEXT FROM dep_cursor INTO @dep_name, @type; END DEALLOCATE dep_cursor DROP TABLE #dependencies SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']' print @sql EXEC sp_executesql @sql 

You can get the constraint names by querying the information_schema system views.

select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed' 

Just to build on Jeremy Stein's answer, I created a stored procedure for this, and set it up so it can be used to delete a column that has or does not have default constraints. It's not real efficient since it's querying sys.columns twice, but it works.

CREATE PROCEDURE [dbo].[RemoveColumnWithDefaultConstraints] -- Add the parameters for the stored procedure here @tableName nvarchar(max), @columnName nvarchar(max) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @ConstraintName nvarchar(200) SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = (@columnName) AND object_id = OBJECT_ID(@tableName)) IF @ConstraintName IS NOT NULL EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @ConstraintName) IF EXISTS(SELECT * FROM sys.columns WHERE Name = @columnName AND Object_ID = Object_ID(@tableName)) EXEC('ALTER TABLE ' + @tableName + ' DROP COLUMN ' + @columnName) END GO 

Looking up the name of the contraint or using MSSQL design view is not always an option. I currently want to make a script for deleting a column with a contraint on it. Using the name is not an option since the name is generated and I want to use the script in different environments Dev/Sys/Prod/etc. Using the name is then not possible because the contraint names will differ per environment. I will probably have to look into the systems tabel but I agree there should be an easier option available.

I believe explicitly dropping the constraints prior to dropping the column is a "cleaner" solution. This way, you don't drop constraints you may not be aware of. If the drop still fails, you know there are additional constraints remaining. I like being in control of exactly what is happening to my database.

Plus, scripting the drops explicitly guarantees the script and hopefully the results to be repeatable in exactly the way you intend.

What do you mean randomly generated? You can look up the constraints on the specific column in management studio or via the sys.tables view and find what the name(s) are.

Then, you can change your script to drop the constraints prior to dropping the column. What type of constraint is this? If it is a foreign key constraint, make sure that doing this won't hurt the data integrity within you database.

I just ran into this. You can delete the column with constraints using MSSQL design view. Right click on the column you want to be dropped (with or without constraints) and you are able to delete this without any problems. Ha.. I looked stupid already.

Just Generate Scripts for the table. There you can find the name of all constraints.