[SQL server] 取得表中有默认值的字段名及默认值(几个系统表的应用)

偏执的太偏执、 2022-05-14 08:21 284阅读 0赞

if object\_id('tb') is not null  
drop table tb  
go  
create table tb(id int,name varchar(50) default 'abc',num int default 5)  
insert into tb(id) select 1  
insert into tb select 1,'oo',100  
insert into tb(id,name) select 1,'oo'  
go  
declare @tbname varchar(50)  
set @tbname='tb'--表名  
select @tbname as tbname,c.name as colname,replace(replace(replace(replace(b.\[text\],'(''',''),''')',''),'((',''),'))','') as defaultvalue  
from sysconstraints a join syscomments b on a.constid=b.id   
join syscolumns c on a.id=c.id and a.colid=c.colid  
where a.id=object\_id(@tbname) and object\_name(a.constid) like '%df%'

执行结果:   tbname      colname     defaultvalue

tb               name          abc

tb                num            5

1.表a(sysconstraints ) 保存数据库所有表的约束,根据表的id来区分是属于哪个表的约束,表sysconstraints 中的主要字段有'表的id','约束本身的id' (a.constid), '此约束在表此中是第几列'.  object\_name(a.constid) like '%df%'是筛选那些约束为"默认值"的约束类型.

2.表b(syscomments ) 保存约束的实际内容,它是根据表a 中的约束id来区分,它可以取得约束的具体值,如默认值"abc".

3.表c (syscolumns) 保存表的各个列的信息,包括列号,列名,类型,长度等等.在这里取的是列名.

出处： [http://346386938.blog.163.com/blog/static/3843942020107101152809/][http_346386938.blog.163.com_blog_static_3843942020107101152809]

[http_346386938.blog.163.com_blog_static_3843942020107101152809]: http://346386938.blog.163.com/blog/static/3843942020107101152809/