www.2cto.com
显示下图中的字段及说明：
脚本如下：SQL2008R2上测试通过。
declare @table_name varchar(50)
set @table_name = 'WF_Direction' --在这里改表名 www.2cto.com
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
