获取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

澳门金沙vip 1澳门金沙vip,SELECT sysobjects.name AS [table], sysproperties.[value] AS 表说明, 
澳门金沙vip 2      syscolumns.name AS field, properties.[value] AS 字段表明, systypes.name AS type, 
澳门金沙vip 3      syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 
澳门金沙vip 4      ‘Scale’卡塔尔(قطر‎, 0卡塔尔 AS 小数位数, syscolumns.isnullable AS isnull, 
澳门金沙vip 5      CASE WHEN syscomments.text IS NULL 
澳门金沙vip 6      THEN ” ELSE syscomments.text END AS [Default], 
澳门金沙vip 7      CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, ‘IsIdentity’) 
澳门金沙vip 8      = 1 THEN ‘√’ ELSE ” END AS 标识, CASE WHEN EXISTS
澳门金沙vip 9          (SELECT 1
澳门金沙vip 10         FROM sysobjects
澳门金沙vip 11         WHERE xtype = ‘PK’ AND name IN
澳门金沙vip 12                   (SELECT name
澳门金沙vip 13                  FROM sysindexes
澳门金沙vip 14                  WHERE indid IN
澳门金沙vip 15                            (SELECT indid
澳门金沙vip 16                           FROM sysindexkeys
澳门金沙vip 17                           WHERE id = syscolumns.id AND colid = syscolumns.colid))) 
澳门金沙vip 18      THEN ‘√’ ELSE ” END AS 主键
澳门金沙vip 19FROM syscolumns INNER JOIN
澳门金沙vip 20      sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
澳门金沙vip 21      systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
澳门金沙vip 22      sysproperties properties ON syscolumns.id = properties.id AND 
澳门金沙vip 23      syscolumns.colid = properties.smallid LEFT OUTER JOIN
澳门金沙vip 24      sysproperties ON sysobjects.id = sysproperties.id AND 
澳门金沙vip 25      sysproperties.smallid = 0 LEFT OUTER JOIN
澳门金沙vip 26      syscomments ON syscolumns.cdefault = syscomments.id
澳门金沙vip 27WHERE (sysobjects.xtype = ‘U’) 
澳门金沙vip 28