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

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 2

You could surely use the SP mentioned above to do it, however, there is an easier way to do it and I believe MGT Studio should be your first choice for making these changes unless you are trying to automate things using a script. Here is how to do it:

1- Right click on the table

2- Click on Design

3- Comment as shown above

Just so you have a complete answer, here is a simpler script to change it (simpler comparing to above answers):

DECLARE @v sql_variant  SET @v = N'Comment here' EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Stack_testing', N'COLUMN', N'testinghere' 

Answer 3

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 4

Unfortunately this approach is not applicable to highly loaded production environments. Stored procedures manipulating extended properties set an exclusive lock on the target table. While they are being executed entire table is unreadable.