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

  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')