数据库字典

这是个数据库字典查询方法.

先保留这两个语句,以后有时间我在详细说明来源.

 

查表字典:

SELECT     a.name AS 表名, b.value AS 描述, c.name AS 构架
FROM         sys.objects AS a LEFT OUTER JOIN
                      sys.extended_properties AS b ON a.object_id = b.major_id LEFT OUTER JOIN
                      sys.schemas AS c ON a.schema_id = c.schema_id
WHERE     (a.type = 'U') AND (b.minor_id = 0)

查字段字典:

select * from sys.extended_properties

SELECT     TOP (100) PERCENT d.name AS 表名, i.name AS 构架, a.colorder AS 字段序号, a.name AS 字段名, (CASE WHEN COLUMNPROPERTY(a.id, a.name,
                      'IsIdentity') = 1 THEN '√' ELSE '' END) AS 标识, (CASE WHEN
                          (SELECT     COUNT(*)
                            FROM          sysobjects
                            WHERE      (name IN
                                                       (SELECT     name
                                                         FROM          sysindexes
                                                         WHERE      (id = a.id) AND (indid IN
                                                                                    (SELECT     indid
                                                                                      FROM          sysindexkeys
                                                                                      WHERE      (id = a.id) AND (colid IN
                                                                                                                 (SELECT     colid
                                                                                                                   FROM          syscolumns
                                                                                                                   WHERE      (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK')) > 0 THEN '√' ELSE '' END) AS 主键,
                      b.name AS 类型, a.length AS 占用字节数, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'),
                      0) AS 小数位数, (CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END) AS 允许空, ISNULL(e.text, '') AS 默认值, ISNULL(g.value, '') AS 字段说明
FROM         sys.syscolumns AS a LEFT OUTER JOIN
                      sys.systypes AS b ON a.xtype = b.xusertype LEFT OUTER JOIN
                      sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
                      sys.objects AS h ON d.id = h.object_id LEFT OUTER JOIN
                      sys.schemas AS i ON h.schema_id = i.schema_id LEFT OUTER JOIN
                      sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
                      sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id
ORDER BY a.id, 字段序号

 

看不明白没关系,慢慢地我会再详细写明如果设置字典.