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 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    www.2cto.com  

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    www.2cto.com  

  

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  

 

 

作者 niniu