科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道获取MS SQL库数据字典的经典SQL语句

获取MS SQL库数据字典的经典SQL语句

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

获取MS SQL库数据字典的经典SQL语句。

作者:cyz1980 来源:CSDN 2008年3月28日

关键字: 语句 SQL 获取 数据库 SQL Server

  • 评论
  • 分享微博
  • 分享邮件
获取MS SQL库数据字典的经典SQL语句

SELECT sysobjects.name AS [table], sysproperties.[value] AS 表说明,
      syscolumns.name AS field, properties.[value] AS 字段说明, systypes.name AS type,
      syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,
      'Scale'), 0) AS 小数位数, syscolumns.isnullable AS isnull,
      CASE WHEN syscomments.text IS NULL
      THEN '' ELSE syscomments.text END AS [Default],
      CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')
      = 1 THEN '√' ELSE '' END AS 标识, 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 主键
FROM syscolumns INNER JOIN
      sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
      systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
      sysproperties properties ON syscolumns.id = properties.id AND
      syscolumns.colid = properties.smallid LEFT OUTER JOIN
      sysproperties ON sysobjects.id = sysproperties.id AND
      sysproperties.smallid = 0 LEFT OUTER JOIN
      syscomments ON syscolumns.cdefault = syscomments.id
WHERE (sysobjects.xtype = 'U')

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章