SQL Comments on Create Table on SQL Server 2008

Question

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.

Answer 1

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

Answer 2

use this sql command

Create table TABLE NAME (ATTRIBUTE NAME (ATTRIBUTE SIZE)) // both create and table are Keywords

Answer 3

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

Answer 4

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

Answer 5

I prefer the GUI when desinging 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

Answer 6

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'