mysql增加表和字段的描述_SQL server 为表和字段添加表描述和字段描述

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

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','字段名'