代码如下：
-
- ----------------------------------------------------------
- -- 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
