2、MSSQL2005 use test--數(shù)據(jù)庫(kù)
go
--2005實(shí)現(xiàn)字段屬性統(tǒng)計(jì)(2000里的系統(tǒng)表sysproperties描述表、字段不存在，2005里用sys.extended_properties視圖替代)
select
[表名]=c.Name,
[表說(shuō)明]=isnull(f.[value],''),
[列名]=a.Name,
[列序號(hào)]=a.Column_id,
[標(biāo)識(shí)]=case when is_identity=1 then '√' else '' end,
[主鍵]=case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in
(select Name from sys.indexes where index_id in
(select indid from sysindexkeys where and colid=a.column_id)))
then '√' else '' end,
[類(lèi)型]=b.Name,
[字節(jié)數(shù)]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'
when b.Name='xml' then ' 2^31-1字節(jié)/2G'
else rtrim(a.[max_length]) end,
[長(zhǎng)度]=ColumnProperty(a.object_id,a.Name,'Precision'),
[小數(shù)]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
[是否為空]=case when a.is_nullable=1 then '√' else '' end,
[列說(shuō)明]=isnull(e.[value],''),
[默認(rèn)值]=isnull(d.text,'')
from
sys.columns a
left join
sys.types b on a.user_type_id=b.user_type_id
inner join
sys.objects c on a.object_id=c.object_id and c.Type='U'
left join
syscomments d on a.default_object_id=d.ID
left join
sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1
left join
sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1
[/code]
結(jié)果：

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。