commenting tables

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

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.

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.

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.

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