检索数据库中表的结构及字段说明

查询数据库中某个表的字段明细及字段的说明。
显示下图中的字段及说明：

脚本如下：SQL2008R2上测试通过。

 
declare @table_name varchar(50)
 set @table_name = 'WF_Direction'  --在这里改表名
 select table_name '表名',column_basic.column_name '列名',column_default '默认值',is_nullable '允许为空',data_type '类型',character_maximum_length '长度',column_explain '注释',case when isnull(column_key.column_name,'') <> '' then 'YES' else '' end as '主键'  
from
   (
     select table_name,column_name ,column_default ,is_nullable ,data_type ,character_maximum_length,ordinal_position 
    from   INFORMATION_SCHEMA.COLUMNS
     where  table_name = @table_name
   ) column_basic 
  left join
   (
     SELECT objname as column_name,value as column_explain
     FROM   ::FN_LISTEXTENDEDPROPERTY(default,'user','dbo','table',@table_name,'column',default)
    ) column_explain on
  column_basic.column_name   COLLATE Chinese_PRC_CI_AS  = column_explain.column_name  COLLATE Chinese_PRC_CI_AS 
   left join
   (
     select column_name 
    from   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
     where  table_name = @table_name
    ) column_key on 
column_basic.column_name  COLLATE Chinese_PRC_CI_AS   = column_key.column_name  COLLATE Chinese_PRC_CI_AS 
 order by ordinal_position