复制代码 代码如下:

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