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

时间：2024-01-15 22:22:20

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

代码如下：

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

相关文章