ms sql动态获取指定表名的字段的字段类型

  (CASE WHEN a.colorder=1 THEN ELSE '' END) N'表名',
  a.colorder N'字段序号', N'字段名',
  (CASE WHEN COLUMNPROPERTY(,, 'IsIdentity')=1 THEN '√'ELSE '' END) N'标识',
    WHEN (
      SELECT COUNT(*) FROM sysobjects WHERE name IN (
        SELECT name FROM sysindexes
        WHERE id = AND
            indid IN (SELECT indid FROM sysindexkeys WHERE id = AND colid in (SELECT colid FROM syscolumns WHERE id = AND name =
      ) AND xtype = 'PK')>0
    THEN '√'
    ELSE '' END
  ) N'主键', N'类型',
  a.length N'占用字节数',
  ISNULL(COLUMNPROPERTY(,,'Scale'),0) AS N'小数位数',
  (CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) N'允许空',
  ISNULL(e.text, '') N'默认值'
FROM syscolumns a
LEFT JOIN systypes b  ON a.xtype=b.xusertype
INNER JOIN sysobjects d ON AND d.xtype='U' AND<>'dtproperties'
LEFT JOIN syscomments e ON
WHERE = 'DOC_ORG' --要查询的表
ORDER BY object_name(, a.colorder