CODEWITHSUNDEEP
sql-servermetadata
BuddyJoe
BuddyJoe

Reputation: 71101

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

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?

Upvotes: 35

Views: 38711

Answers (4)

Sergey B.
Sergey B.

Reputation: 163

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.

Upvotes: 3

LearnByReading
LearnByReading

Reputation: 1883

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: enter image description here

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'

Upvotes: 21

JoshBerke
JoshBerke

Reputation: 67068

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

Upvotes: 37

keithwarren7
keithwarren7

Reputation: 14280

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.

Upvotes: 4

Related Questions