今天突然遇到有人要数据库表及表字段说明，数据库表太多又不能一个个表去找，就想想SQL是否能直接查询出来。
经过查询资料，加上一些自己的一些调整写了一个sql语句，在此记录一下，以方便日后查找使用。
SELECT ( CASE WHEN a.colorder = 1 THEN d.name
ELSE ''
END ) N'表名' ,
( CASE WHEN a.colorder = 1 THEN ISNULL(h.value, '')
ELSE ''
END ) N'表说明' ,
a.colorder N'字段序号' ,
a.name N'字段名' ,
( CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END ) N'标识' ,
( 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 ) N'主键' ,
b.name N'类型' ,
a.length N'占用字节数' ,
COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS N'长度' ,
ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS N'小数位数' ,
( CASE WHEN a.isnullable = 1 THEN '√'
ELSE ''
END ) N'允许空' ,
ISNULL(e.text, '') N'默认值' ,
ISNULL(g.[value], '') AS N'字段说明'
FROM sys.syscolumns a
LEFT JOIN sys.systypes b ON a.xtype = b.xusertype
INNER JOIN sys.sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
LEFT JOIN sys.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
--AND g.name='MS_Description'
LEFT JOIN sys.extended_properties h ON h.major_id = a.id
AND h.minor_id = 0
AND h.name = 'MS_Description' --表扩展属性 名称
ORDER BY OBJECT_NAME(a.id) ,
a.colorder;
补充一些表及表及字段描述增删改SQL操作语句：
--表（Department） /******添加********/
--为表添加描述信息
execute sys.sp_addextendedproperty N'MS_Description',N'部门表',N'Schema',N'dbo',N'table',N'Department',null,null
go
--为字段添加描述信息
execute sys.sp_addextendedproperty N'MS_Description',N'部门表ID',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark'
go
/******添加********/ /******修改********/
--把表 Department 的扩展属性（表的描述）原值 ‘部门表’改为 ‘部门表测试修改’
execute sp_updateextendedproperty N'MS_Description',N'部门表测试修改',N'Schema',N'dbo',N'table',N'Department',null,null --把表 Department 的列 D_Remark 的扩展属性（列的描述）原值 ‘部门描述’改为 ‘部门详细描述’
execute sp_updateextendedproperty N'MS_Description',N'部门详细描述',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark'
/******修改********/ /******删除********/
--删除表 Department 的扩展属性（表的描述）
execute sp_dropextendedproperty N'MS_Description',N'Schema',N'dbo',N'table',N'Department',null,null --删除表 Department 的列 D_Remark 的扩展属性（列的描述）
execute sp_dropextendedproperty N'MS_Description',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark'
/******删除********/