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

版权声明：本文为博主原创文章，未经博主允许不得转载。