sql语句 得到 sql server 表中列的类型和说明

通过Sql语句获得 列的类型和说明:

  得到的表头：表名|列名|类型|说明

 

  1：针对sql server 2000，在sql2000中，使用：sysobjects,syscolumns和sysproperties表

    SELECT o.name as table_name,c.name AS col_name,type_name(c.xtype) AS type_name,isnull(p.value,'') AS col_Property FROM sysobjects AS o JOIN syscolumns AS c  ON o.id = c.id LEFT JOIN sysproperties p ON c.id = p.id and c.colid=p.smallid WHERE o.name = 'TableName' ORDER BY c.colid

  2：针对sql server 2005，在sql2005中，使用：sys.objects,sys.columns和sys.extended_properties表

    SELECT o.name as table_name,c.name AS col_name,TYPE_NAME(c.user_type_id) AS type_name,isnull(ep.value,'') AS col_Property FROM sys.objects AS o JOIN sys.columns AS c  ON o.object_id = c.object_id 

LEFT JOIN sys.extended_properties ep ON c.object_id = ep.major_id and c.column_id=ep.minor_id

WHERE o.name = 'TableName' ORDER BY c.column_id