代码如下：
------------------------ mp_DropColConstraint-- 功能：删除某个表的某列的所有约束-- 入口：-- @TableName NVARCHAR(128) -- 表名-- @ColumnName NVARCHAR(128) -- 列名----------------------if OBJECT_ID(N'dbo.mp_DropColConstraint', N'P') is not null drop procedure dbo.mp_DropColConstraintgocreate procedure dbo.mp_DropColConstraint @TableName NVARCHAR(128), @ColumnName NVARCHAR(128)asbegin 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 [email protected] and [email protected] -- 查询默认值约束 declare @cdefault int, @cname varchar(128) select @cdefault=cdefault from sys.syscolumns where [email protected] and id=OBJECT_ID(@TableName) select @cname=name from sys.sysobjects where [email protected] 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 [email protected] exec('alter table ' + @tablename + ' drop constraint ' + @cname) set @i = @i + 1 end drop table #tendgo------------------------------------------- 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_IsColumnExistsgocreate function dbo.mfn_IsColumnExists(@TableName NVARCHAR(128), @ColumnName NVARCHAR(128)) returns bitasbegin declare @rt bit set @rt=0 if (select name from sys.syscolumns where [email protected] and id=OBJECT_ID(@TableName)) is not null set @rt=1 return @rtendgo---------------------------------------------------- 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_GetColumnIndexesgocreate function dbo.mfn_GetColumnIndexes(@TableName NVARCHAR(128), @ColumnName NVARCHAR(128)) returns @ret table ( id int, name NVARCHAR(128) )asbegin declare @tid int, @colid int -- 先查询出表id和列id select @tid=OBJECT_ID(@tablename) select @colid=colid from sys.syscolumns where [email protected] and [email protected] -- 查询出索引名称 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 [email protected] and [email protected] returnendgo------------------------------------------------------ mp_DropColumnIndexes-- 功能：删除指定列的所有索引-- 入口：-- @TableName NVARCHAR(128) 表名-- @ColumnName NVARCHAR(128) 列名--------------------------------------------------if OBJECT_ID(N'dbo.mp_DropColumnIndexes', N'P') is not null drop procedure dbo.mp_DropColumnIndexesgocreate procedure dbo.mp_DropColumnIndexes @TableName NVARCHAR(128), @ColumnName NVARCHAR(128)asbegin 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<[email protected] begin select @idxname=name from #t EXEC('drop index ' + @idxname + ' on ' + @tablename) set @[email protected]+1 end drop table #tendgo-------------------------------------------------- mp_DropColConstraintAndIndex-- 功能：删除指定字段的所有约束和索引-- 入口：-- @TableName NVARCHAR(128) -- 表名-- @ColumnName NVARCHAR(128) -- 列名------------------------------------------------if OBJECT_ID(N'dbo.mp_DropColConstraintAndIndex', N'P') is not null drop procedure dbo.mp_DropColConstraintAndIndexgocreate procedure dbo.mp_DropColConstraintAndIndex @TableName NVARCHAR(128), @ColumnName NVARCHAR(128)asbegin exec dbo.mp_DropColConstraint @TableName, @ColumnName exec dbo.mp_DropColumnIndexes @TableName, @ColumnNameendgo