Как вы удаляете столбец с неопределенным значением ограничения по умолчанию и неизвестными индексами

Что такое метод определения, существует ли ограничение по умолчанию для столбца и его имени, а также имена любых индексов, чтобы отбросить их перед удалением столбца?

Выполнение этой задачи будет выполнено следующим образом.

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].'[email protected]) and dobj.name is not null and col.name = @ColumnName) if(isnull(@ConstraintName, '') <> '') exec('alter table [dbo].['[email protected]+'] drop constraint ['[email protected]+']') 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].['[email protected]+'] drop constraint ['[email protected]+']') else exec('drop index [dbo].['[email protected]+'].['[email protected]+']') 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].'[email protected])) and (xtype = 'U'))) and (exists (select * from [dbo].syscolumns where (id = object_id('[dbo].'[email protected])) and (name = @ColumnName))) begin exec [dbo]._spDropIndexesForColumn @TableName, @ColumnName exec [dbo]._spDropDefaultValueConstraint @TableName, @ColumnName exec('alter table [dbo].['[email protected]+'] drop column ['[email protected]+']') end GO 

Затем легко вызвать следующее:

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

Я не рассматривал ограничения внешнего ключа, поскольку мы их не используем, но, возможно, они также могут быть включены.

Давайте будем гением компьютера.