用sql 语句给字段添加描述

时间:2023-03-08 15:43:58

用sql 语句给字段添加描述
IF not exists
(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'strTableName', 'column', ''strColumnName''))
BEGIN
exec sp_addextendedproperty
'MS_Description', 'strColumnDesc ', 'user', 'dbo', 'table','strTableName', 'column', 'strColumnName'
END
ELSE

BEGIN
exec sp_updateextendedproperty 'MS_Description', 'strColumnDesc ', 'user', 'dbo', 'table','strTableName', 'column', 'strColumnName'
END

用sql语句查询字段描述
SELECT * FROM ::fn_listextendedproperty
(null,'user','dbo','table','APPL_CREDIT','column',null)

用sql语句删除字段描述
EXEC sys.sp_dropextendedproperty
'MS_Description' ,'user','dbo','table','APPL_CREDIT','column','APPLNO'

1> sql server 怎么查询数据字段说明?
可以在extended_properties视图中进行查看:
sql:SELECT * FROM sys.extended_properties WHERE major_id = OBJECT_ID ('TABLENAME' );
解释:以上语句就是从视图中获取到对象的所有描述信息。并且表名必须用大写(TABLANAME换成相应的表名)