这篇文章中有好的答案。补充一点,'MS_Description' 的值也可以是其他内容。例如,我们可以使用'SourceDescription'来描述数据源的详细信息,'TableDescription'用于表格,'ColumnDescription'用于每个表格列。
示例:
create table testTablename(
id int,
name varchar(20),
registerNumber bigint
)
EXEC sys.sp_addextendedproperty
@name=N'SourceDescription',
@value=N'Result of process x union y ' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'testTableName'
EXEC sys.sp_addextendedproperty
@name=N'TableDescription',
@value=N'Table is used for send email to clients.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'testTableName'
EXECUTE sp_addextendedproperty
@name = 'ColumnDescription',
@value = 'Unique identification of employer. Its the registry of company too.',
@level0type = 'SCHEMA',
@level0name= N'dbo',
@level1type = N'TABLE',
@level1name = N'testTableName',
@level2type = N'COLUMN',
@level2name = N'registerNumber'
exec sp_dropextendedproperty
@name = 'ColumnDescription',
@level0type = 'SCHEMA',
@level0name= N'dbo',
@level1type = N'TABLE',
@level1name = N'testTableName',
@level2type = N'COLUMN',
@level2name = N'registerNumber'
select
tables.name tableName,
tables.create_date,
tables.modify_date,
tableDesc.value TableDescription,
sourceDesc.value SourceDescription
from
sys.tables
left join sys.extended_properties tableDesc on tables.object_id = tableDesc.major_id and tableDesc.name = 'TableDescription'
left join sys.extended_properties sourceDesc on tables.object_id = sourceDesc.major_id and sourceDesc.name = 'SourceDescription'
where
tableDesc.name in('TableDescription', 'SourceDescription', 'ColumnDescription')
order by tables.name
select
tables.name tableName,
columns.name columnName,
extended_properties.value
from
sys.tables
inner join sys.columns on tables.object_id = columns.object_id
left join sys.extended_properties on
tables.object_id = extended_properties.major_id
and columns.column_id = extended_properties.minor_id
and extended_properties.name in('MS_Description','ColumnDescription')
where
tables.name = 'testTableName'