--模糊查询表
select * from (select
TABLE_NAME from information_schema.tables)
as bc where TABLE_NAME like '%TableName%'
--1、添加表说明
EXECUTE sp_addextendedproperty N'MS_Description','用户表',N'user',N'dbo',N'table',N'表名称_User',NULL,NULL
--2、删除表说明
EXEC sp_dropextendedproperty N'MS_Description','user','dbo','table', '表名称_User', NULL,NULL
--3.添加字段说明
EXECUTE sp_addextendedproperty N'MS_Description', '名称', N'user', N'dbo', N'table', N'Table_User', N'column', N'字段名称_name'
--4.删除字段说明
EXEC sp_updateextendedproperty 'MS_Description','名称','user','dbo','table','Table_User','column',N'字段名称_name'
--获取所有表、及表说明
select
a.name AS 表名,
CONVERT(NVARCHAR(100), isnull(g.[value], '')) AS 说明
from sys.tables a left
join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0)
--获取表字段、及字段说明
select
c.[name] as 字段名,
cast(isnull(ep.[value], '') as varchar(100)) as[字段说明]
from sys.tables as t
inner join sys.columns
as c on t.object_id = c.object_id
left join sys.extended_properties as ep
on ep.major_id = c.object_id and ep.minor_id = c.column_id
where t.name='TableName'