question

vihrao avatar image
vihrao asked

commenting tables

How can I comment inside my tables or any place in sql 2005 db?
tablecomments
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What is it, some sort of competition between the two of you?
1 Like 1 ·
2 mins... +1 :)
0 Likes 0 ·
+1, Data dictionaries aren't comments within each table itself, but they're useful and simple to use too.
0 Likes 0 ·
Stap me vitals, you guys are quick with the answers.
0 Likes 0 ·
@Phil - they are too quick for me sometimes!
0 Likes 0 ·
Show more comments
Matt Whitfield avatar image
Matt Whitfield answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered
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/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
@Phil - Which tool?
0 Likes 0 ·
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/
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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.)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
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 GO IF OBJECT_ID('.', 'U') IS NOT NULL DROP TABLE . GO CREATE TABLE . ( [_id] [int] IDENTITY(1,1) NOT NULL, [creator] [varchar](80) NOT NULL CONSTRAINT [DF__creator] DEFAULT (user_name()), [insertiondate] [datetime] NOT NULL CONSTRAINT [DF__insertiondate] DEFAULT (getdate()), [terminationdate] [datetime] NULL CONSTRAINT PK__ PRIMARY KEY (_id) ) GO -- Add description to table object EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA', @level0name=N'', @level1type=N'TABLE', @level1name=N'' 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'', @level1type=N'TABLE', @level1name=N'', @level2type=N'COLUMN', @level2name=N'_id' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date the record was created (automatic)' , @level0type=N'SCHEMA', @level0name=N'', @level1type=N'TABLE', @level1name=N'', @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'', @level1type=N'TABLE', @level1name=N'', @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'', @level1type=N'TABLE', @level1name=N'', @level2type=N'COLUMN', @level2name=N'creator' GO
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I haven't implemented it, but using a DDL-trigger for "automatic" documentation of objects is doable, and there's even examples in AdventureWorks of it. Look at this simple example of a DDL-trigger: ALTER TRIGGER ddlDocumentationTest ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS BEGIN SET NOCOUNT ON DECLARE @data XML SET @data = EVENTDATA(); declare @t nvarchar(max) set @t=@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') print @t END Issuing the T-SQL statement: create table ttt (t int identity(1,1)) would give the output > create table ttt (t int identity(1,1)) Modify the DDL-trigger to actually do something more useful than printing the T-SQL issued. Look at the documentation EVENTDATA() and you'll get a lot of meta information about a DDL-statement. There's an example of a more meaningful DDL-trigger in the AdventureWorks database in SQL Server 2005, which stores DDL in a database table.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you specifically want to add comments to objects, you could define a syntax for comments (eg putting them in a inside a T-sql-comment) and parse the specific text-snippet from the T-SQL, and store that in either your own Comments-table or storing it using sp_AddExtendedProperty
0 Likes 0 ·
Comments are already tagged, either by being /\* \*/ or -- ! It isn't too hard to parse this, but getting the comments attached to the right column is always going to be a bit of a complication.
0 Likes 0 ·
@Phil Factor: Yes, they sure are. But if one wants a way to store documentation, one would need a syntax inside of the comments, not just parse all comments and try to figure out what they mean. I would go for some sort of semi-XML syntax, with parametrized tags.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.