How do you delete a column with an undefined default constraint value and unknown indexes

Question

What is a method for determining if there is a default constraint for a column and its name, as well as the names of any indexes to remove them before the column is deleted?

Answer 1

This task will be completed as follows:

if (exists (select * from [dbo].sysobjects where (id = object_id(N'[dbo]._spDropDefaultValueConstraint')) and (xtype = 'P'))) drop procedure [dbo]._spDropDefaultValueConstraint GO create procedure [dbo]._spDropDefaultValueConstraint @TableName varchar(256), @ColumnName varchar(256) as /* This proc will drop the default value constraint on a column even when you don't know what its name is. */ declare @ConstraintName varchar(256) set @ConstraintName = ( select dobj.name from sys.columns col left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' where col.object_id = object_id('[dbo].' +@TableName ) and dobj.name is not null and col.name = @ColumnName) if(isnull(@ConstraintName, '') <> '') exec('alter table [dbo].[' +@TableName +'] drop constraint [' +@ConstraintName +']') GO ------------------------------------------------------------------------------------------- if (exists (select * from [dbo].sysobjects where (id = object_id(N'[dbo]._spDropIndexesForColumn')) and (xtype = 'P'))) drop procedure [dbo]._spDropIndexesForColumn GO create procedure [dbo]._spDropIndexesForColumn @TableName varchar(256), @ColumnName varchar(256) as /* This proc will drop all indexes on a column, both indexes and unique constraints as well as multi-part indexes that reference it. */ declare @IndexName varchar(256) declare @IsPrimaryKey bit declare @IsUniqueConstraint bit declare crIndexes cursor for select ind.name, ind.is_primary_key, ind.is_unique_constraint from sys.indexes ind inner join sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id inner join sys.tables t on ind.object_id = t.object_id where t.name = @TableName and col.name = @ColumnName open crIndexes fetch next from crIndexes into @IndexName, @IsPrimaryKey, @IsUniqueConstraint while(@@fetch_status = 0) begin if(@IsPrimaryKey = 1) or (@IsUniqueConstraint = 1) exec('alter table [dbo].[' +@TableName +'] drop constraint [' +@IndexName +']') else exec('drop index [dbo].[' +@TableName +'].[' +@IndexName +']') fetch next from crIndexes into @IndexName, @IsPrimaryKey, @IsUniqueConstraint end close crIndexes deallocate crIndexes GO ------------------------------------------------------------------------------------------- if (exists (select * from [dbo].sysobjects where (id = object_id(N'[dbo]._spDropColumn')) and (xtype = 'P'))) drop procedure [dbo]._spDropColumn GO create procedure [dbo]._spDropColumn @TableName varchar(256), @ColumnName varchar(256) as /* This proc will drop a column (first dropping the default value constraint and any indexes if they exist) if it exists. */ if (exists (select * from [dbo].sysobjects where (id = object_id('[dbo].' +@TableName )) and (xtype = 'U'))) and (exists (select * from [dbo].syscolumns where (id = object_id('[dbo].' +@TableName )) and (name = @ColumnName))) begin exec [dbo]._spDropIndexesForColumn @TableName, @ColumnName exec [dbo]._spDropDefaultValueConstraint @TableName, @ColumnName exec('alter table [dbo].[' +@TableName +'] drop column [' +@ColumnName +']') end GO 

Then it is easy to call the following:

 exec [dbo]._spDropColumn 'TableName', 'ColumnName' 

I did not consider foreign key restrictions, since we do not use them, but perhaps they can also be included.