SELECT
[表名]= case when a.colorder=1 ``then d. name else '' end ,
[表说明]= case when a.colorder=1 ``then isnull (f.value, '' ) else '' end ,
[字段序号]=a.colorder,
[字段名]=a. name ,
[标识]= case when COLUMNPROPERTY( a.id,a. name , 'IsIdentity' )=1 then '√' else '' end ,
[主键]= case when exists( SELECT 1 ``FROM sysobjects ``where xtype= 'PK' and parent_obj=a.id ``and name in (
SELECT name FROM sysindexes WHERE indid ``in (
SELECT indid ``FROM sysindexkeys WHERE id = a.id ``AND colid=a.colid
))) ``then '√' else '' end ,
[类型]=b. name ,
[占用字节数]=a.length,
[长度]=COLUMNPROPERTY(a.id,a. name , 'PRECISION' ),
[小数位数]= isnull (COLUMNPROPERTY(a.id,a. name , 'Scale' ),0),
[允许空]= case when a.isnullable=1 ``then '√' else '' end ,
[默认值]= isnull (e.text, '' ),
[字段说明]= isnull (g.[value], '' )
FROM syscolumns a
left join systypes b ``on a.xusertype=b.xusertype
inner join sysobjects d ``on a.id=d.id ``and d.xtype= 'U' and d. name <> 'dtproperties'
left join syscomments e ``on a.cdefault=e.id
left join sys.extended_properties g ``on a.id=g.major_id ``and a.colid=g.minor_id
left join sys.extended_properties f ``on d.id=f.major_id ``and f.minor_id=0
--where d.name='TableName' --如果只查询指定表,加上此条件
order by a.id,a.colorder
OR
select
c. name as cname,c.prec ``AS Prec,c.isnullable ``AS isnullable,
[IsPk]= case when exists( SELECT 1 ``FROM sysobjects ``where xtype= 'PK' and parent_obj=c.id ``and name in (
SELECT name FROM sysindexes ``WHERE indid ``in ( SELECT indid ``FROM sysindexkeys ``WHERE id = c.id ``AND colid=c.colid))) then '1' else '0' end ,
[defaultval]= isnull (e.text, '' ),
t. name as tname ,( select value ``from sys.extended_properties ``as ex ``where ex.major_id = c.id and ex.minor_id = c.colid) as notes
from
syscolumns as c ``inner join sys.tables ``as ta ``on c.id=ta.object_id
inner join ( select name ,system_type_id ``from sys.types ``where name <> 'sysname' ) as t ``on c.xtype=t.system_type_id
left join syscomments e ``on c.cdefault=e.id
where
ta. name = 'TestTb' order by c.colid
