Is it possible to add a description/comment to a table in Microsoft SQL 2000+

Question

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

How would you do this through the CREATE TABLE statement?

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

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

Answer 1

Most tools and people use the Extended Properties for supporting this. The common name used by SSMS is MS_Description

There are several built in stored procedures for creating these properties as well as reading them, they procs changed over time so there will be compat differences between SQL versions.

Answer 2

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>'

You can update them:

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);

or

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

Answer 3

If you wish to add highlight and notes, to any kind of database (SQL, MySQL, Excel ) you can use a tool called Marie -Alix , it will open any kind of data and will show it in tables and then you can do whatever you want with it..