MSSQL如何删除字段的所有约束和索引
代码如下：
[sql]
----------------------------------------------------------
-- mp_DropColConstraint
-- 功能：删除某个表的某列的所有约束
-- 入口： www.2cto.com
-- @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) www.2cto.com
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)) www.2cto.com
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)
) www.2cto.com
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