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/