x

commenting tables

How can I comment inside my tables or any place in sql 2005 db?
more ▼

asked Oct 08, 2010 at 09:04 AM in Default

vihrao gravatar image

vihrao
31 1 1 1

Thank you all. I successfully used the extended properties 'MS_Description'. However can I get an example how to do this in the table creation script.
Oct 08, 2010 at 07:21 PM vihrao
I've added a second answer giving an SSMS template that one then use in order to create a new table. it gives you bits of script that you can then reuse to build up the table. Remember to save the template as a template and us Cntl Shift M to fill in all the place-holders.
Oct 09, 2010 at 10:26 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

You can use extended properties. They're available on most objects within the database and the database itself.

This example is from Books Online:

EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Scalar function returning the quantity of inventory for a specified ProductID.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = ufnGetStock;
GO
more ▼

answered Oct 08, 2010 at 09:19 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

2 mins... +1 :)
Oct 08, 2010 at 09:22 AM Matt Whitfield ♦♦
What is it, some sort of competition between the two of you?
Oct 08, 2010 at 09:26 AM ThomasRushton ♦
+1, Data dictionaries aren't comments within each table itself, but they're useful and simple to use too.
Oct 08, 2010 at 09:28 AM Mark
Stap me vitals, you guys are quick with the answers.
Oct 08, 2010 at 09:34 AM Phil Factor
@Phil - they are too quick for me sometimes!
Oct 08, 2010 at 09:37 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

The problem (I hope I've guessed right) is that if you comment tables, columns and indexes, the comments disappear because SQL Server does not retain the creation script as it does for all the routines such as triggers, stored procedures and functions.

You are supposed to use extended properties to do this. In SSMS, you usually have to get to the 'properties' context window and fill in the Extended properties with the 'MS_Description' name. (e.g. for a table, you right-click on the table in the object browser, click on properties, and when the window eventually pops up, you then click on extended properties, and add a name 'MS_Description' and the value should be the comment you wish to add.

Of course this is so time-consuming that very few people do it this way. you can do it via the system stored procedures sp_addextendedproperty (Adds a new extended property to a database object), sp_dropextendedproperty (Removes an extended property from a database object), and sp_updateextendedproperty (you guessed it. Updates the value of an existing extended property) and there is a function fn_listextendedproperty. I like [William Brewer's article Towards the Self-Documenting SQL Server Database][1].

Of course, this lot is likely to drive you mad, but you can cook up a template to do the basic table documentation for you.

I have to admit that I use a third-party tool to do this.

[1]: http://www.simple-talk.com/sql/sql-tools/towards-the-self-documenting-sql-server-database/
more ▼

answered Oct 08, 2010 at 09:33 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

@Phil - Which tool?
Oct 08, 2010 at 09:38 AM ThomasRushton ♦
I like Steve McCabe's SQLTac on http://www.sqltac.com/ but I also use SQLDoc. The advantage of SQLTac is that it nags you when you've missed putting documentation in.
Oct 08, 2010 at 09:43 AM Phil Factor
I've done a table-source builder on Simple-Talk that puts all the comments back into the table build script. http://www.simple-talk.com/sql/t-sql-programming/exploring-sql-server-table-metadata-with-ssms-and-tsql/
Oct 08, 2010 at 09:45 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

When you use the table designed in management studio, you get MS_Description extended properties on tables and columns.

Alternatively, there are many documentation tools that can do that for you.
more ▼

answered Oct 08, 2010 at 09:22 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

There have been numerous good comments about extended properties, and they are well said and I agree.

My personal preferred technique though is to keep that outside of the database and store it in the creation script for the tables which I keep in my source control.

(Of course, I could also see how it would be nice if SQL Server retained the entire creation script for the tables including the comments just as it does for views and SPs, but even then I would find that to be a convenience that augments the source control, not a replacement for it.)
more ▼

answered Oct 08, 2010 at 10:20 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

Thank you all. I successfully used the extended properties 'MS_Description'. However can I get an example how to do this in the table creation script.
Oct 08, 2010 at 07:20 PM vihrao
(comments are locked)
10|1200 characters needed characters left

OK. Here is a table template I use to get started with a new table. I then use what is produced by the Cntl Shift M in order to add the other colums and delete anything I don't want.

    -- =========================================
    -- Create table template
    -- =========================================
    USE <database, sysname, MyDatabase>
    GO

    IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
      DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
    GO
    CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
    (
       [<table_name, sysname, sample_table>_id] [int] IDENTITY(1,1) NOT NULL,
       [creator] [varchar](80) NOT NULL CONSTRAINT [DF_<table_name, sysname, sample_table>_creator]  DEFAULT (user_name()),
       [insertiondate] [datetime] NOT NULL CONSTRAINT [DF_<table_name, sysname, sample_table>_insertiondate]  DEFAULT (getdate()),
       [terminationdate] [datetime] NULL
        CONSTRAINT PK_<schema_name, sysname, dbo>_<table_name, sysname, sample_table> PRIMARY KEY (<table_name, sysname, sample_table>_id)
    )
    GO
    -- Add description to table object
    EXEC sys.sp_addextendedproperty 
       @name=N'MS_Description', 
       @value=N'<table_description_value,,Table description here>' ,
       @level0type=N'SCHEMA', 
       @level0name=N'<schema_name, sysname, dbo>', 
       @level1type=N'TABLE', 
       @level1name=N'<table_name, sysname, sample_table>'
    GO

    -- Add description to a specific column
    EXEC sys.sp_addextendedproperty 
       @name=N'MS_Description', 
       @value=N'primary key for the table' ,
       @level0type=N'SCHEMA', 
       @level0name=N'<schema_name, sysname, dbo>', 
       @level1type=N'TABLE', 
       @level1name=N'<table_name, sysname, sample_table>', 
       @level2type=N'COLUMN', 
       @level2name=N'<table_name, sysname, sample_table>_id'
    GO
    EXEC sys.sp_addextendedproperty 
       @name=N'MS_Description', 
       @value=N'Date the record was created (automatic)' ,
       @level0type=N'SCHEMA', 
       @level0name=N'<schema_name, sysname, dbo>', 
       @level1type=N'TABLE', 
       @level1name=N'<table_name, sysname, sample_table>', 
       @level2type=N'COLUMN', 
       @level2name=N'insertiondate'
    GO
    EXEC sys.sp_addextendedproperty 
       @name=N'MS_Description', 
       @value=N'Date for the termination of the record' ,
       @level0type=N'SCHEMA', 
       @level0name=N'<schema_name, sysname, dbo>', 
       @level1type=N'TABLE', 
       @level1name=N'<table_name, sysname, sample_table>', 
       @level2type=N'COLUMN', 
       @level2name=N'Terminationdate'
    GO
    EXEC sys.sp_addextendedproperty 
       @name=N'MS_Description', 
       @value=N'Creator of the record' ,
       @level0type=N'SCHEMA', 
       @level0name=N'<schema_name, sysname, dbo>', 
       @level1type=N'TABLE', 
       @level1name=N'<table_name, sysname, sample_table>', 
       @level2type=N'COLUMN', 
       @level2name=N'creator'
    GO
more ▼

answered Oct 09, 2010 at 10:23 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x84
x9

asked: Oct 08, 2010 at 09:04 AM

Seen: 3483 times

Last Updated: Oct 08, 2010 at 09:04 AM