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 |