获取MSSQL数据字典的SQL语句

复制代码 代码如下:

create view dbo.vw_db_dictionary

as

select top 100 percent dbo.sysobjects.name as table_name,

dbo.sysproperties.[value] as table_desc, dbo.syscolumns.name as field,

properties.[value] as field_desc, dbo.systypes.name as field_type,

dbo.syscolumns.length as field_size,

isnull(columnproperty(dbo.syscolumns.id, dbo.syscolumns.name, ‘scale’), 0)

as field_precision, dbo.syscolumns.isnullable as nullable,

case when syscomments.text is null

then ” else syscomments.text end as default_value,

case when columnproperty(syscolumns.id, syscolumns.name, ‘isidentity’)

= 1 then ‘√’ else ” end as is_identity, case when exists

(select 1

from sysobjects

where xtype = ‘pk’ and name in

(select name

from sysindexes

where indid in

(select indid

from sysindexkeys

where id = syscolumns.id and colid = syscolumns.colid)))

then ‘√’ else ” end as is_key

from dbo.syscolumns inner join

dbo.sysobjects on dbo.sysobjects.id = dbo.syscolumns.id inner join

dbo.systypes on dbo.syscolumns.xtype = dbo.systypes.xtype left outer join

dbo.sysproperties properties on dbo.syscolumns.id = properties.id and

dbo.syscolumns.colid = properties.smallid left outer join

dbo.sysproperties on dbo.sysobjects.id = dbo.sysproperties.id and

dbo.sysproperties.smallid = 0 left outer join

dbo.syscomments on dbo.syscolumns.cdefault = dbo.syscomments.id

where (dbo.sysobjects.xtype = ‘u’)

order by dbo.sysobjects.name