SQL comments on create table on SQL Server 2008

I need to create some pretty big tables in SQL Server 2008. While I do have SQL Server Management Studio (SSMS), I would like to comment the tables and the columns when I create the ta开发者_高级运维ble. How do I do this?

An example of the query I am running:

CREATE TABLE cert_Certifications
(
  certificationID int PRIMARY KEY IDENTITY,
  profileID int,
  cprAdultExp datetime null
)

I've tried COMMENT'Expiration Date for the Adult CPR' and COMMENT='Expiration Date for the Adult CPR' after the data type, and SQL Server is giving me an error.

---

This is what I use

/*==============================================================*/
/* Table: TABLE_1                                               */
/*==============================================================*/
create table TABLE_1 (
   ID                   int                  identity,
   COLUMN_1             varchar(10)          null,
   COLUMN_2             varchar(10)          null,
   constraint PK_TABLE_1 primary key nonclustered (ID)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'This is my table comment',
   'user', @CurrentUser, 'table', 'TABLE_1'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'This is the primary key comment',
   'user', @CurrentUser, 'table', 'TABLE_1', 'column', 'ID'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'This is column one comment',
   'user', @CurrentUser, 'table', 'TABLE_1', 'column', 'COLUMN_1'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'This is column 2 comment',
   'user', @CurrentUser, 'table', 'TABLE_1', 'column', 'COLUMN_2'
go

---

You can put comments on both tables and columns by creating what are called Extended Properties. You can put extended properties at both the table level and column level. This can be done via T-SQL or SSMS.

For example, in T-SQL it looks something like this:

sp_addextendedproperty 'BackColor', 'Red', 'user', '<schema name>', 'table', '<table name', 'column', '<column name>'.

You can read more about it on sp_addextendedproperty (Transact-SQL).

---

I prefer the GUI when designing tables because I can visualize the layout better. In the GUI designer, one can add a description for the table and columns in the properties window as shown in the image below

---

There are good answers in this post. Adding that the value 'MS_Description' could be other thing. For example, we can use 'SourceDescription' for details about the source the data, 'TableDescription' for table and 'ColumnDescription' for each column on table.

Example:

-- Create example table
create table testTablename(
    id int,
    name varchar(20),
    registerNumber bigint
)

-- SourceDescription
EXEC sys.sp_addextendedproperty 
    @name=N'SourceDescription', 
    @value=N'Result of process x union y ' , -- Comment about the source this data. 
    @level0type=N'SCHEMA',
    @level0name=N'dbo', 
    @level1type=N'TABLE',
    @level1name=N'testTableName' -- Name of Table

-- TableDescription
EXEC sys.sp_addextendedproperty 
    @name=N'TableDescription', 
    @value=N'Table is used for send email to clients.' , -- Coment about the used of table
    @level0type=N'SCHEMA',
    @level0name=N'dbo', 
    @level1type=N'TABLE',
    @level1name=N'testTableName'

-- ColumnDescription
EXECUTE sp_addextendedproperty 
    @name = 'ColumnDescription', 
    @value = 'Unique identification of employer. Its the registry of company too.', 
    @level0type = 'SCHEMA', 
    @level0name= N'dbo', 
    @level1type = N'TABLE', 
    @level1name = N'testTableName', 
    @level2type = N'COLUMN', 
    @level2name = N'registerNumber'

-- If necessary, you can delete the comment.
exec sp_dropextendedproperty
    @name = 'ColumnDescription', 
    @level0type = 'SCHEMA', 
    @level0name= N'dbo', 
    @level1type = N'TABLE', 
    @level1name = N'testTableName', 
    @level2type = N'COLUMN', 
    @level2name = N'registerNumber'


-- Show you the table resume
select 
    tables.name tableName,
    tables.create_date,
    tables.modify_date,
    tableDesc.value TableDescription,
    sourceDesc.value SourceDescription
from 
    sys.tables  
    left join sys.extended_properties tableDesc on tables.object_id = tableDesc.major_id and tableDesc.name = 'TableDescription'
    left join sys.extended_properties sourceDesc on tables.object_id = sourceDesc.major_id and sourceDesc.name = 'SourceDescription'
where 
    tableDesc.name in('TableDescription', 'SourceDescription', 'ColumnDescription')
order by tables.name


-- show you the columns resume
select 
    tables.name tableName,
    columns.name columnName,
    extended_properties.value
from 
    sys.tables 
    inner join sys.columns on tables.object_id = columns.object_id
    left join sys.extended_properties on 
        tables.object_id = extended_properties.major_id 
        and columns.column_id = extended_properties.minor_id
        and extended_properties.name in('MS_Description','ColumnDescription')
where
    tables.name = 'testTableName'

---

You need to use the stored procedure called sp_addextendedproperty to add comments to columns/tables in SQL Server.

---

Although it does not directly answer original question (J Henzel and Randy Minder already did!) I would like to share something else I just wrote that can be very useful for those who have to comment a lot of tables and columns.

The following queries:

-- Generate comments templates for all tables
SELECT 
'EXEC sys.sp_addextendedproperty
    @name=N''TableDescription'',
    @level0type=N''SCHEMA'',    
    @level1type=N''TABLE'',
    @level0name=N''' + TABLE_SCHEMA + ''',
    @level1name=N''' + TABLE_NAME + ''',
    @value=N''TODO'';'
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME NOT like 'sys%'
order by TABLE_SCHEMA, TABLE_NAME


-- Generate comments templates for all columns
SELECT 'EXECUTE sp_addextendedproperty 
    @name = ''ColumnDescription'', 
    @level0type = ''SCHEMA'', 
    @level1type = N''TABLE'', 
    @level2type = N''COLUMN'', 
    @level0name=N''' + TABLE_SCHEMA + ''',
    @level1name=N''' + TABLE_NAME + ''',
    @level2name = N''' + COLUMN_NAME + ''',
    @value = ''TODO'';'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA not like 'sys%' and TABLE_NAME not like 'sysdiagrams%'
  order by TABLE_SCHEMA, TABLE_NAME, case when ORDINAL_POSITION = 1 then '0' else COLUMN_NAME end

Will produce in SQL Server output a list of calls to sp_addextendedproperty for all the tables and all the columns existing in your database, by querying on system tables to gather them.

Of course, it will not comment it magically for you, but at least you just have to fill the "TODOs" placeholders with the relevant comment for all objects you would like to describe and to execute it.

It avoids you to manually write all the calls and saves a lot of time, and with it you can't forget a table or column, so I hope it will be useful for somebody else.

Side remarks:

Just beware on the filters in WHEREs on "sys": it's here to exclude system objects, but depending on your objects names, you may need a bit of fine-tuning of you have tables named alike.

Also, there isn't any comment at all in my DB, so my query returns all tables/columns. It does not consider whether there's already a comment or not on it.

---

Use this SQL command:

Create table TABLE NAME (ATTRIBUTE NAME (ATTRIBUTE SIZE)) // Both create and table are keywords