MSSQL 如何删除字段的所有约束和索引-阿里云开发者社区

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

2013-11-11 767

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 原文MSSQL 如何删除字段的所有约束和索引代码如下： [sql] view plaincopy ---------------------------------------------------------- -- mp_DropColConstra...

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

代码如下：

[sql] view plain copy

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
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
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
24. -- 查询默认值约束
25. declare @cdefault int, @cname varchar(128)
26. select @cdefault=cdefault from sys.syscolumns where name=@ColumnName and id=OBJECT_ID(@TableName)
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
32. declare @i int, @imax int
33. select @i=1, @imax=max(id) from #t
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
42. drop table #t
44. end
46. go
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
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
72. go
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
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
98. -- 先查询出表id和列id
99. select @tid=OBJECT_ID(@tablename)
100. select @colid=colid from sys.syscolumns where id=@tid and name=@columnname
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
107. return
108. end
110. go
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
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. )
138. insert into #t select * from mfn_GetColumnIndexes(@TableName, @ColumnName)
140. -- 删除索引
141. declare @i int, @imax int, @idxname nvarchar(128)
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
151. drop table #t
152. end
154. go
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
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
176. go

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景