how to add a comment to a table or column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

how to add a comment to a table or column

Author

Topic

mirchi
Starting Member

1 Post

Posted - 2004-07-22 : 05:57:37

Hi,

I would like to know how to add a comment to a table or column in SQLServer. In Oracle we do so as follows,

to add a comment for the table:

comment on table <table_name> is 'new comment'

to add a comment for the column:

comment on column <column_name> is 'comment';

to view the comments

select * from User_tab_comments; --> to view the table comments

select * from user_col_comments; --> to view the column comments

thanks in advance

Sathya

Cheers

slacker
Posting Yak Master

115 Posts

Posted - 2004-07-22 : 06:48:34

Extended properties. I dont remember the exact syntax off hand and im too lazy to create a sample so instead ill just cut and paste some info for you.

sp_addextendedproperty adds a new extended property to a database object
sp_dropextendedproperty removes an extended property from a database object
sp_updateextendedproperty updates the value of an existing extended property
fn_listextendedproperty retrieves the value of an extended property or the list of all extended properties from a database object

Look up those functions in the sql online books. And check out this tutorial.

http://developer.com/db/article.php/3361751

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 06:51:14

Yeah, Oracle does that nicely. In SQL Server its lousy.

There is a sysproperties table that can store "properties" about things.

In Enterprise Manager [GUI] table "Design Table" you can store a comment on a Table or a Column.

In Query Analyser you can RightClick an object in the Object Browser tree and do "properties" to maintain properties, but now you ahve to know the "code" for the property.

Other that that you have to call an SProc:

For a column:

EXECUTE sp_addextendedproperty N'MS_Description', 'My Column Comment', N'user', N'dbo', N'table', N'MyTableName', N'column', N'MyColumnName'

(Note that this is to ADD, there is a different SProc to UPDATE)

For a table:

EXECUTE sp_updateextendedproperty N'MS_Description', 'My Table Comment, N'user', N'dbo', N'table', N'MyTableName', NULL, NULL

(This is to update, rather than Add)

They can be listed with

SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'MyTable', 'column', ['MyColumnName' | DEFAULT])

It's basically Crying Out for a wrapper SProc that does an ADD or UPDATE depending whether it already exists, and hides all the other parameters.

Kristen

sathyav
Starting Member

27 Posts

Posted - 2004-07-22 : 07:30:51

Thank u Slacker and Kristen

lemme tryout the options u provided.

Cheers
Sathya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources