How to display table and its fields property

-- 数据库表结构详细信息查看
select
[name] 表名,'' 字段名,'' 数据类型,'' 实际长度,'' 字段特性,'' 字段序号,'' 是否自增列,'' 是否计算列 ,
isnull((select value from sys.extended_properties p where p.name= 'MS_Description'
and p.class='1' and p.major_id = o.id and p.minor_id=0 ), '') as 备注
from sysobjects o where o.type = 'U'
union

SELECT o.name 表名,c.name 字段名,t.name +
case t.name
when 'nvarchar' then '('+ case convert(varchar(10), c.prec) when '-1' then 'MAX' else convert(varchar(10), c.prec) end +')'
when 'nchar' then '('+ convert(varchar(10), c.prec)+')'
when 'char' then '('+ convert(varchar(10), c.prec)+')'
when 'varchar' then '('+ case convert(varchar(10), c.prec) when '-1' then 'MAX' else convert(varchar(10), c.prec) end +')'
when 'decimal' then '('+ convert(varchar(10), c.prec)+','+ convert(varchar(10), c.scale)+')'
when 'numeric' then '('+ convert(varchar(10), c.prec)+','+ convert(varchar(10), c.scale)+')'
else ''
end as 数据类型,
case when c.length = '-1' then 'MAX' else convert(varchar(10), c.length) end as 实际长度,
case
when (c.isnullable =0 and k.colid>0) then '主键'
when c.isnullable =0 then '非空'
else '' end as 字段特性,
--convert(varchar(10), c.colid) 字段序号,
c.colid 字段序号,
case when COLUMNPROPERTY(c.id,c.NAME,'IsIdentity') = 1 then '是' else '' end as 是否自增列,
case when COLUMNPROPERTY(c.id,c.NAME,'IsComputed') = 1 then '是' else '' end as 是否计算列,
--isnull(convert(varchar(10),k.colid), '') as 主键序号,
isnull((select value from sys.extended_properties p
where p.name= 'MS_Description' and p.class='1' and p.major_id = c.id
and p.minor_id=c.colorder ),'' ) as 备注
FROM syscolumns c
join sysobjects o on o.id = c.id and o.type = 'U'
left join systypes t on c.xusertype = t.xusertype
left join sysindexes i on i.id = c.id and exists(select 1 from sysobjects where xtype = 'pk' and name = i.name)
left join sysindexkeys k on k.id = c.id and i.indid = k.indid and k.colid = c.colid
order by 表名, 字段序号

Running against differenct database ,you will get different result.