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

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

代码如下：

[sql] view plaincopy

1. 1. ----------------------------------------------------------  
   2. --  mp_DropColConstraint  
   3. --  功能：删除某个表的某列的所有约束  
   4. --  入口：  
   5. --      @TableName NVARCHAR(128)    -- 表名  
   6. --      @ColumnName NVARCHAR(128)   -- 列名  
   7. ----------------------------------------------------------  
   8. if OBJECT_ID(N'dbo.mp_DropColConstraint', N'P') is not null  
   9.     drop procedure dbo.mp_DropColConstraint  
   10. go  
   11.   
   12. create procedure dbo.mp_DropColConstraint  
   13.     @TableName NVARCHAR(128),  
   14.     @ColumnName NVARCHAR(128)  
   15. as  
   16. begin  
   17.     if OBJECT_ID(N'#t', N'TB') is not null  
   18.         drop table #t  
   19.       
   20.     -- 查询主键约束、非空约束等  
   21.     select ROW_NUMBER() over(order by CONSTRAINT_NAME) id, CONSTRAINT_NAME into #t from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_CATALOG=DB_NAME()  
   22.         and TABLE_NAME=@TableName and COLUMN_NAME=@ColumnName  
   23.           
   24.     -- 查询默认值约束  
   25.     declare @cdefault int, @cname varchar(128)  
   26.     select @cdefault=cdefault from sys.syscolumns where name=@ColumnName and id=OBJECT_ID(@TableName)  
   27.               
   28.     select @cname=name from sys.sysobjects where id=@cdefault  
   29.     if @cname is not null  
   30.         insert into #t select coalesce(max(id), 0)+1, @cname from #t      
   31.   
   32.     declare @i int, @imax int  
   33.     select @i=1, @imax=max(id) from #t  
   34.   
   35.     while @i <= @imax  
   36.     begin  
   37.         select @cname=CONSTRAINT_NAME from #t where id=@i  
   38.         exec('alter table ' + @tablename + ' drop constraint ' + @cname)  
   39.         set @i = @i + 1   
   40.     end  
   41.   
   42.     drop table #t  
   43.   
   44. end  
   45.   
   46. go  
   47.   
   48. -----------------------------------------  
   49. --  mfn_IsColumnExists  
   50. --  功能：判断字段是否存在  
   51. --  入口：  
   52. --      @TableName NVARCHAR(128)    -- 表名  
   53. --      @ColumnName NVARCHAR(128)       -- 列名  
   54. --  出口：  
   55. --      BIT  1=存在，0=不存在  
   56. ----------------------------------------  
   57. if OBJECT_ID(N'dbo.mfn_IsColumnExists', N'FN') is not null  
   58.     drop function dbo.mfn_IsColumnExists  
   59. go  
   60.   
   61. create function dbo.mfn_IsColumnExists(@TableName NVARCHAR(128), @ColumnName NVARCHAR(128))  
   62.     returns bit  
   63. as  
   64. begin  
   65.     declare @rt bit  
   66.     set @rt=0  
   67.     if (select name from sys.syscolumns where name=@ColumnName and id=OBJECT_ID(@TableName)) is not null  
   68.         set @rt=1  
   69.     return @rt  
   70. end  
   71.   
   72. go  
   73.   
   74. --------------------------------------------------  
   75. --  mfn_GetColumnIndexes  
   76. --  功能：查询某个字段的所有索引  
   77. --  入口：  
   78. --      @TableName NVARCHAR(128) -- 表名  
   79. --      @ColumnName NVARCHAR(128) -- 列名（字段名）  
   80. --  出口：返回一个结果集：  
   81. --      id int -- 序号，从1开始  
   82. --      name nvarchar(128) -- 索引名称  
   83. --------------------------------------------------  
   84. if OBJECT_ID(N'dbo.mfn_GetColumnIndexes', N'TF') is not null  
   85.     drop function dbo.mfn_GetColumnIndexes  
   86. go  
   87.   
   88. create function dbo.mfn_GetColumnIndexes(@TableName NVARCHAR(128), @ColumnName NVARCHAR(128))  
   89.     returns @ret table  
   90.     (  
   91.         id int,  
   92.         name NVARCHAR(128)  
   93.     )  
   94. as  
   95. begin  
   96.     declare @tid int, @colid int  
   97.   
   98.     -- 先查询出表id和列id  
   99.     select @tid=OBJECT_ID(@tablename)  
   100.     select @colid=colid from sys.syscolumns where id=@tid and name=@columnname  
   101.   
   102.     -- 查询出索引名称  
   103.     insert into @ret select ROW_NUMBER() OVER(ORDER BY cols.index_id) as id, inds.name idxname from sys.index_columns cols  
   104.         left join sys.indexes inds on cols.object_id=inds.object_id and cols.index_id=inds.index_id   
   105.         where cols.object_id=@tid and column_id=@colid  
   106.           
   107.     return  
   108. end  
   109.   
   110. go  
   111.   
   112.   
   113. --------------------------------------------------  
   114. --  
   115. --  mp_DropColumnIndexes  
   116. --  功能：删除指定列的所有索引  
   117. --  入口：  
   118. --      @TableName NVARCHAR(128) 表名  
   119. --      @ColumnName NVARCHAR(128) 列名  
   120. --------------------------------------------------  
   121. if OBJECT_ID(N'dbo.mp_DropColumnIndexes', N'P') is not null  
   122.     drop procedure dbo.mp_DropColumnIndexes  
   123. go  
   124.   
   125. create procedure dbo.mp_DropColumnIndexes  
   126.     @TableName NVARCHAR(128),  
   127.     @ColumnName NVARCHAR(128)  
   128. as  
   129. begin  
   130.     if OBJECT_ID(N'#t', N'TB') is not null  
   131.         drop table #t  
   132.     create table #t  
   133.     (  
   134.         id int,       
   135.         name nvarchar(128)  
   136.     )  
   137.       
   138.     insert into #t select * from mfn_GetColumnIndexes(@TableName, @ColumnName)  
   139.       
   140.     -- 删除索引  
   141.     declare @i int, @imax int, @idxname nvarchar(128)  
   142.       
   143.     select @i=1, @imax=COALESCE(max(id), 0) from #t  
   144.     while @i<=@imax   
   145.     begin  
   146.         select @idxname=name from #t  
   147.         EXEC('drop index ' + @idxname + ' on ' + @tablename)  
   148.         set @i=@i+1  
   149.     end  
   150.       
   151.     drop table #t  
   152. end  
   153.   
   154. go  
   155.   
   156. ------------------------------------------------  
   157. --  mp_DropColConstraintAndIndex  
   158. --  功能：删除指定字段的所有约束和索引  
   159. --  入口：  
   160. --      @TableName NVARCHAR(128)    -- 表名  
   161. --      @ColumnName NVARCHAR(128)   -- 列名  
   162. ------------------------------------------------  
   163. if OBJECT_ID(N'dbo.mp_DropColConstraintAndIndex', N'P') is not null  
   164.     drop procedure dbo.mp_DropColConstraintAndIndex  
   165. go  
   166.   
   167. create procedure dbo.mp_DropColConstraintAndIndex  
   168.     @TableName NVARCHAR(128),  
   169.     @ColumnName NVARCHAR(128)  
   170. as  
   171. begin  
   172.     exec dbo.mp_DropColConstraint @TableName, @ColumnName  
   173.     exec dbo.mp_DropColumnIndexes @TableName, @ColumnName  
   174. end  
   175.   
   176. go