欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

获取MSSQL数据字典的SQL语句

程序员文章站 2023-01-04 17:26:44
复制代码 代码如下:create view dbo.vw_db_dictionary as select top 100 percent dbo.sysobjects.na...
复制代码 代码如下:

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