[点晴永久免费OA]如何查询Sql Server中所有默认值约束并删除它们

admin

2019年9月28日 20:48 本文热度 3056

今天遇到一个问题，就是要将某数据库中所有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

查询指定表cs_test2_list中字段time7是否存在约束值：

select a.name 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=''time7'' and tablename=''cs_test2_list''

该文章在 2019/9/28 20:48:02 编辑过

关键字查询

所有

默认

server

删除

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。