MSSQL 如何删除字段的所有约束和索引

代码如下：

----------------------------------------------------------
--	mp_DropColConstraint
--	功能：删除某个表的某列的所有约束
--	入口：
--		@TableName NVARCHAR(128)	-- 表名
--		@ColumnName NVARCHAR(128)	-- 列名
----------------------------------------------------------
if OBJECT_ID(N'dbo.mp_DropColConstraint', N'P') is not null
	drop procedure dbo.mp_DropColConstraint
go

create procedure dbo.mp_DropColConstraint
	@TableName NVARCHAR(128),
	@ColumnName NVARCHAR(128)
as
begin
	if OBJECT_ID(N'#t', N'TB') is not null
		drop table #t
	
	-- 查询主键约束、非空约束等
	select ROW_NUMBER() over(order by CONSTRAINT_NAME) id, CONSTRAINT_NAME into #t from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_CATALOG=DB_NAME()
		and TABLE_NAME=@TableName and COLUMN_NAME=@ColumnName
		
	-- 查询默认值约束
	declare @cdefault int, @cname varchar(128)
	select @cdefault=cdefault from sys.syscolumns where name=@ColumnName and id=OBJECT_ID(@TableName)
			
	select @cname=name from sys.sysobjects where id=@cdefault
	if @cname is not null
		insert into #t select coalesce(max(id), 0)+1, @cname from #t	

	declare @i int, @imax int
	select @i=1, @imax=max(id) from #t

	while @i <= @imax
	begin
		select @cname=CONSTRAINT_NAME from #t where id=@i
		exec('alter table ' + @tablename + ' drop constraint ' + @cname)
		set @i = @i + 1	
	end

	drop table #t

end

go

-----------------------------------------
--	mfn_IsColumnExists
--	功能：判断字段是否存在
--	入口：
--		@TableName NVARCHAR(128)	-- 表名
--		@ColumnName NVARCHAR(128)		-- 列名
--	出口：
--		BIT  1=存在，0=不存在
----------------------------------------
if OBJECT_ID(N'dbo.mfn_IsColumnExists', N'FN') is not null
	drop function dbo.mfn_IsColumnExists
go

create function dbo.mfn_IsColumnExists(@TableName NVARCHAR(128), @ColumnName NVARCHAR(128))
	returns bit
as
begin
	declare @rt bit
	set @rt=0
	if (select name from sys.syscolumns where name=@ColumnName and id=OBJECT_ID(@TableName)) is not null
		set @rt=1
	return @rt
end

go

--------------------------------------------------
--	mfn_GetColumnIndexes
--	功能：查询某个字段的所有索引
--	入口：
--		@TableName NVARCHAR(128) -- 表名
--		@ColumnName NVARCHAR(128) -- 列名（字段名）
--	出口：返回一个结果集：
--		id int -- 序号，从1开始
--		name nvarchar(128) -- 索引名称
--------------------------------------------------
if OBJECT_ID(N'dbo.mfn_GetColumnIndexes', N'TF') is not null
	drop function dbo.mfn_GetColumnIndexes
go

create function dbo.mfn_GetColumnIndexes(@TableName NVARCHAR(128), @ColumnName NVARCHAR(128))
	returns @ret table
	(
		id int,
		name NVARCHAR(128)
	)
as
begin
	declare @tid int, @colid int

	-- 先查询出表id和列id
	select @tid=OBJECT_ID(@tablename)
	select @colid=colid from sys.syscolumns where id=@tid and name=@columnname

	-- 查询出索引名称
	insert into @ret select ROW_NUMBER() OVER(ORDER BY cols.index_id) as id, inds.name idxname from sys.index_columns cols
		left join sys.indexes inds on cols.object_id=inds.object_id and cols.index_id=inds.index_id 
		where cols.object_id=@tid and column_id=@colid
		
	return
end

go


--------------------------------------------------
--
--	mp_DropColumnIndexes
--	功能：删除指定列的所有索引
--	入口：
--		@TableName NVARCHAR(128) 表名
--		@ColumnName NVARCHAR(128) 列名
--------------------------------------------------
if OBJECT_ID(N'dbo.mp_DropColumnIndexes', N'P') is not null
	drop procedure dbo.mp_DropColumnIndexes
go

create procedure dbo.mp_DropColumnIndexes
	@TableName NVARCHAR(128),
	@ColumnName NVARCHAR(128)
as
begin
	if OBJECT_ID(N'#t', N'TB') is not null
		drop table #t
	create table #t
	(
		id int,		
		name nvarchar(128)
	)
	
	insert into #t select * from mfn_GetColumnIndexes(@TableName, @ColumnName)
	
	-- 删除索引
	declare @i int, @imax int, @idxname nvarchar(128)
	
	select @i=1, @imax=COALESCE(max(id), 0) from #t
	while @i<=@imax 
	begin
		select @idxname=name from #t
		EXEC('drop index ' + @idxname + ' on ' + @tablename)
		set @i=@i+1
	end
	
	drop table #t
end

go

------------------------------------------------
--	mp_DropColConstraintAndIndex
--	功能：删除指定字段的所有约束和索引
--	入口：
--		@TableName NVARCHAR(128)	-- 表名
--		@ColumnName NVARCHAR(128)	-- 列名
------------------------------------------------
if OBJECT_ID(N'dbo.mp_DropColConstraintAndIndex', N'P') is not null
	drop procedure dbo.mp_DropColConstraintAndIndex
go

create procedure dbo.mp_DropColConstraintAndIndex
	@TableName NVARCHAR(128),
	@ColumnName NVARCHAR(128)
as
begin
	exec dbo.mp_DropColConstraint @TableName, @ColumnName
	exec dbo.mp_DropColumnIndexes @TableName, @ColumnName
end

go