使用SQL语句查询表及表字段类型说明

今天突然遇到有人要数据库表及表字段说明，数据库表太多又不能一个个表去找，就想想SQL是否能直接查询出来。

经过查询资料，加上一些自己的一些调整写了一个sql语句，在此记录一下，以方便日后查找使用。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

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操作语句：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

--表（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' /******删除********/