Title: how may I generate a script containing only the exec sp_addextendedproperty statements for table and column comments using a SQL Server 2008 model?
Description:
I have tables and columns in a SQL Server 2008 model. I would like to generate only the EXEC sp_addextendedproperty DDL for the comments for those tables and columns, withoutproducing any create table statements. How may I accomplish that?
Solution:
That may be accomplished by taking the following steps:
1. Save the model.
2. Close the saved model, and reopen it (this step is important).
3. Add comments for the tables and columns in the re-opend model.
4. Go to Actions | Forward Engineer | Alter Script, and make sure that the comments box under Other Options is selected.
5. Press Preview, and you will see the EXEC sp_addextendedproperty DDL for the comments for those tables and columns.
E.g. for a model where a table name is named 'Test', and the column names are 'a' and 'b', the following DDL would be generated:
EXEC sp_addextendedproperty
@name = 'MS_Description', @value = 'This is a Table comment.',
@level0type = 'SCHEMA', @level0name = 'dbo',
@level1type = 'TABLE', @level1name = 'Test'
go
EXEC sp_addextendedproperty
@name = 'MS_Description', @value = 'This is a column comment a. ',
@level0type = 'SCHEMA', @level0name = 'dbo',
@level1type = 'TABLE', @level1name = 'Test',
@level2type = 'COLUMN', @level2name = 'a'
go
EXEC sp_addextendedproperty
@name = 'MS_Description', @value = 'This is a column comment b. ',
@level0type = 'SCHEMA', @level0name = 'dbo',
@level1type = 'TABLE', @level1name = 'Test',
@level2type = 'COLUMN', @level2name = 'b'
go
Author: Sistek M.
