欢迎来到.net学习网

欢迎联系站长一起更新本网站!QQ:879621940

您当前所在位置:首页 » Sql随手笔记 » 正文

如何查询Sql Server中所有默认值约束并删除它们

创建时间:2015年04月15日 16:53  阅读次数:(8214)
分享到:
今天遇到一个问题,就是要将某数据库中所有PNumber列删除,这个数据库基本上所有表都有这个字段,所以我写了一段sql来删除所有的PNumber列,如下:
declare @columnname nvarchar(1000)
declare my_cur cursor local for 
select b.name from sys.syscolumns a
inner join sys.tables b on a.id=b.object_id 
where a.name='PreNumber'

open my_cur
fetch next from my_cur into @columnname
while @@fetch_status=0
begin  
exec ('alter table '+@columnname+' drop column PreNumber') 
fetch next from my_cur into @columnname
end

close my_cur
deallocate my_cur

sql是没有问题的,但执行的时候报了类似下面的错误:
消息 5074,级别 16,状态 1,第 2 行
对象'DF_XXXX_PNumber_Default' 依赖于 列'PNumber'。
消息 4922,级别 16,状态 9,第 2 行
由于一个或多个对象访问此列,ALTER TABLE DROP COLUMN Creator 失败。


原因就是创建PNumber列的时候为PNumber列创建了默认值,所以我们通过sql命令删除时会要求我们先删除对应的默认值约束(如果直接在设计器中删除不会有此要求,设计器会同时删除对应的约束)。

那么我们要怎么找出数据库中所有表中PNumber列的默认值约束呢?这时候就需要利用sys.default_constraints目录视图了。

sys.default_constraints目录视图


我们所有定义的默认值都可以通过这个目录视图查询出来,sys.default_constraints中有几个重要的列:
1,Name 约束名称
2,parent_object_id 所属表的表标识
3,parent_column_id 默认值对应列的列标识
4,definition 默认值的定义
5,is_system_named 约束名称是不是自已定义的, 0代表是自己定义的,1代表是系统定义的。

有了这些信息,我可以链接sys.columns表与object_name函数查出默认值对应的表名与列名,sql如下:
select name as 默认值名称,
object_name(t.parent_object_id) as 表名,
(select sys.columns.name from sys.columns 
where sys.columns.column_id=t.parent_column_id 
and sys.columns.object_id=t.parent_object_id) as 列名,
t.definition from sys.default_constraints t

如同批量删除列一样,我们可以写一个游标,循环删除所有的默认值。
declare @name varchar(100)
declare @tablename varchar(100)
declare my_cur cursor local for 
select a.name,a.tablename from 
(select name,
object_name(t.parent_object_id) as tableName,
(select sys.columns.name from sys.columns 
where sys.columns.column_id=t.parent_column_id 
and sys.columns.object_id=t.parent_object_id) as columnName,
t.definition from sys.default_constraints t) a where columnname='PNumber'

open my_cur
fetch next from my_cur into @name,@tablename
while @@fetch_status=0
begin  
exec ('alter table '+@tablename+' drop constraint '+@name) 
fetch next from my_cur into @name,@tablename
end

close my_cur
deallocate my_cur
"
来源:.net学习网
说明:所有来源为 .net学习网的文章均为原创,如有转载,请在转载处标注本页地址,谢谢!
【编辑:Wyf

打赏

取消

感谢您的支持,我会做的更好!

扫码支持
扫码打赏,您说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

最新评论

共有评论0条
  • 暂无任何评论,请留下您对本文章的看法,共同参入讨论!
发表评论:
留言人:
内  容:
请输入问题 94+44=? 的结果(结果是:138)
结  果: