question

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

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.

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/

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

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

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.