SQL Server 2000 - How do I add a description to a field via SQL?

I know how to add the description through the server management studio. And i came across some EXEC thing someplace that looked overly complex to add a field description.

in mysql i can just add the COMMENT 'field comment here'开发者_如何学C option to each field in the table create statement... does SQL Server 2000 have such a thing?

Unfortunately that overly complicated exec is the only "proper" way to add a description to a column in a MS SQL DB.

The procedure is:

sp_addextendedproperty

Once you get your head around it it is a fairly simple procedure to use.

here's an example

create table dbo.sometable(col1 int, col2 int)

exec sp_addextendedproperty 'MS_Description', 'comments for col1','user', dbo, 'table', sometable, 'column', col1
exec sp_addextendedproperty 'MS_Description', 'comments for col2','user', dbo, 'table', sometable, 'column', col2

If you want to use a TSQL statement to add a description to a column on a table, try this:

EXECUTE sp_addextendedproperty N'MS_Description', 
        'my description', N'USER', N'dbo', N'TABLE', N'MyTableName', 
        N'COLUMN', N'MyColumnName'

See: Using SQL Server 2000 Extended Properties.

You can go through the trouble to use extended properties as mentioned here if you want, but why not just create a table that contains all of that information - a "data dictionary"?