sql server 中擷取字段預設值SQL

擷取所有的預設值清單

select

object_name(c.id) as 表名

 ,c.name as 字段名

 ,t.name as 資料類型

 ,c.prec as 長度

 ,p.value as 字段說明

 ,m.text as 預設值

from syscolumns c

 inner join

 systypes t

 on c.xusertype=t.xusertype

 left join

 sysproperties p

 on c.id=p.id and c.colid = p.smallid

 left join

 syscomments m

 on c.cdefault=m.id

研究了很久 終于搞定了

擷取單個字段的預設值清單

select

object_name(c.id) as 表名

 ,c.name as 字段名

 ,t.name as 資料類型

 ,c.prec as 長度

 ,p.value as 字段說明

 ,m.text as 預設值

from syscolumns c

 inner join

 systypes t

 on c.xusertype=t.xusertype

 left join

 sysproperties p

 on c.id=p.id and c.colid = p.smallid

 left join

 syscomments m

 on c.cdefault=m.id

where objectproperty(c.id,'IsUserTable')=1

 and object_name(c.id) = 'T_good' and c.name = 'ishot'