是否可以在Microsoft SQL 2000+中向表添加描述/注释 [英] Is it possible to add a description/comment to a table in Microsoft SQL 2000+

问题描述

是否可以在Microsoft SQL 2000及更高版本的表中添加类似元数据"的描述或注释?

Is it possible to add a "metadata"-like description or comments to a table in Microsoft SQL 2000 and above?

您如何通过CREATE TABLE语句来做到这一点?

How would you do this through the CREATE TABLE statement?

是否可以在字段中添加描述或注释?

Is it possible to add a description or comment to fields?

如何在MSSQL 2000中查询此信息? 2005年?

How do you query this info back in MSSQL 2000? 2005?

推荐答案

使用扩展属性.例如，要将扩展属性添加到dbo模式中的表中，可以使用:

Use extended properties. For example to add an extended property to a table in the dbo schema you can use:

EXEC sys.sp_addextendedproperty @name=N'<NameOfProp>', 
@value=N'<Value>' , @level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'<Table>'

您可以更新它们:

EXEC sys.sp_updateextendedproperty 
   @name=N'MS_Description', @value=N'My Description' ,
     @level0type=N'SCHEMA',@level0name=N'dbo', 
      @level1type=N'TABLE'
     ,@level1name=N'<YOUR TABLE NAME>'

您可以按以下方式阅读它们:

You can read them like:

SELECT *
FROM fn_listextendedproperty (NULL, 'schema','dbo', 'table', '<yourtable>', default, default);

或

SELECT
p.name AS [Name],p.value
FROM
sys.tables AS tbl
INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
WHERE
(tbl.name=N'<yourtablename>' and SCHEMA_NAME(tbl.schema_id)=N'dbo')
ORDER BY
[Name] ASC

这篇关于是否可以在Microsoft SQL 2000+中向表添加描述/注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！