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

2022-04-20 12:52:14

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


代码如下：

[sql] view plaincopy
    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 
上一篇：记账本小程序7天开发记录（第七天）


下一篇：线程强制运行|学习笔记