数据字典

SELECT

sysobjects.name AS 表名称 ,
--sysproperties.[value] AS 表说明 ,
syscolumns.name AS 字段名称 ,
--properties.[value] AS 字段说明 ,
systypes.name AS 字段类型 ,
syscolumns.length AS 字段长度 ,
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,'Scale'), 0) AS 小数位数 ,

CASE WHEN syscolumns.isnullable=0
 THEN ''
ELSE ' √ '
END AS 是否为空 ,
CASE WHEN syscomments.text IS NULL
 THEN '' ELSE syscomments.text
END AS 缺省值 ,
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')= 1
 THEN ' √ ' ELSE ''
END AS 递增字段 ,
CASE WHEN sysindexes.name IS NULL
 THEN ''
ELSE sysindexes.name
END AS 索引名称 ,
CASE WHEN sysindexkeys.keyno IS NULL
 THEN ''
ELSE CONVERT(VARCHAR(10),sysindexkeys.keyno )
END AS 索引位置 ,
CASE WHEN sysindexes.indid=1
 THEN ' 聚集索引 '
WHEN sysindexes.indid>1 AND sysindexes.indid<>255
 THEN ' 非聚集索引 '
WHEN sysindexes.indid IS NULL
 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 主键 ,
CASE WHEN sysforeignkeys.constid IS NULL
 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
LEFT OUTER JOIN sysindexkeys                -- 索引中的键或列的信息
  ON sysindexkeys.id = syscolumns.id
 AND sysindexkeys.colid = syscolumns.colid
LEFT OUTER JOIN sysindexes                  -- 数据库 索引表
  ON sysindexes.id = sysindexkeys.id
 AND sysindexes.indid = sysindexkeys.indid
LEFT OUTER JOIN sysforeignkeys
  ON sysforeignkeys.fkeyid = syscolumns.id
 AND sysforeignkeys.fkey = syscolumns.colid
WHERE (sysobjects.xtype = 'U')
order by sysobjects.id,syscolumns.colid

转载于:https://www.cnblogs.com/qingyanxiaochen/p/7493944.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值