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, I would like to comment the tables and the columns when I create the table. How do I do this?

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.

Solution 1:

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

Solution 2:

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 here