表结构数据字典---查询表结构明细信息

╰半夏微凉° 2022-06-18 01:17 294阅读 0赞

SELECT     TOP (100) PERCENT d.name AS 表名, a.id AS 表编号, a.colorder AS 字段序号,  
   a.colid AS 字段说明字段序号, a.name AS 字段名,  
   CASE WHEN COLUMNPROPERTY(a.id, a.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 = a.id AND colid = a.colid)))  
      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 INNER JOIN   
                      sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' 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 表名, 字段序号

--查询含有指定类型的所有表及对应的字段.例如 'varchar','nvarchar','char','nchar'  
select a.name as columnname,object\_name(a.id)as tablename from sys.syscolumns a,  
sys.sysobjects b,  
sys.systypes c  
where a.id=b.id    
and a.xtype=c.xtype  
and b.xtype='u'  
and c.name in('varchar','nvarchar','char','nchar')