SELECT ( CASE WHEN a.colorder = 1 THEN d.name
ELSE ‘‘
END ) AS 表名 ,--如果表名相同就返回空
a.colorder AS 字段序号 ,
a.name AS 字段名 ,
( CASE WHEN COLUMNPROPERTY(a.id, a.name, ‘IsIdentity‘) = 1 THEN ‘√‘
ELSE ‘‘
END ) AS 标识 ,
( CASE WHEN ( SELECT COUNT(*)
FROM sysobjects--查询主键
WHERE ( name IN (
SELECT name
FROM sysindexes
WHERE ( id = a.id )
AND ( indid IN (
SELECT indid
FROM sysindexkeys
WHERE ( id = a.id )
AND ( colid IN (
SELECT
colid
FROM
syscolumns
WHERE
( id = a.id )
AND ( name = a.name ) ) ) ) ) ) )
AND ( xtype = ‘PK‘ )
) > 0 THEN ‘√‘
ELSE ‘‘
END ) AS 主键 ,--查询主键END
b.name AS 类型 ,
a.length AS 占用字节数 ,
COLUMNPROPERTY(a.id, a.name, ‘PRECISION‘) AS 长度 ,
ISNULL(COLUMNPROPERTY(a.id, a.name, ‘Scale‘), 0) AS 小数位数 ,
( CASE WHEN a.isnullable = 1 THEN ‘√‘
ELSE ‘‘
END ) AS 允许空 ,
ISNULL(e.text, ‘‘) AS 默认值 ,
ISNULL(g.[value], ‘‘) AS 字段说明
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = 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
where d.name in(‘T_GC_SuperviseCo‘,‘T_GC_Supervisor‘,‘T_CC_SupervisorAllocate‘)
ORDER BY a.id ,
a.colorder;


---表说明
select
ROW_NUMBER() OVER (ORDER BY a.object_id) AS No,
a.name AS 表名,
isnull(g.[value],‘-‘) AS 说明
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0);

--增加表说明
EXECUTE sp_addextendedproperty N‘MS_Description‘,‘表说明‘,N‘user‘,N‘dbo‘,N‘table‘,N‘表名‘,NULL,NULL

--修改：

EXECUTE sp_updateextendedproperty N‘MS_Description‘,‘表说明‘,N‘user‘,N‘dbo‘,N‘table‘,N‘表名‘,NULL,NULL