SQL server 数据字典（字段描述） & 添加字段描述表描述

清疚 2022-04-11 03:14 636阅读 0赞

## **查看当前数据库中已存在描述的表与字段：** ##

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   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.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
            LEFT   JOIN sys.extended_properties f ON d.id = f.major_id
                                                     AND f.minor_id = 0

sql 执行结果可以直接导出execl

**添加表说明：**

EXECUTE sp_addextendedproperty   N'MS_Description','表说明',N'user',N'dbo',N'table',N'表名',NULL,NULL

**删除表说明：**

EXEC sp_dropextendedproperty N'MS_Description','user','dbo','table', '表名', NULL,NULL

**添加字段说明：**

EXECUTE   sp_addextendedproperty   N'MS_Description','字段说明内容',N'user',N'dbo',N'table',N'表名',N'column',N'字段名'

**删除字段说明：**

EXEC sp_dropextendedproperty N'MS_Description', 'user','dbo', 'table', '表名', 'column','字段名'