获取MSSQL数据字典的SQL语句
前端之家收集整理的这篇文章主要介绍了
获取MSSQL数据字典的SQL语句，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
<div class="codetitle"><a style="CURSOR: pointer" data="50120" class="copybut" id="copybut50120" onclick="doCopy('code50120')"> 代码如下:
<div class="codebody" id="code50120">
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
