We help IT Professionals succeed at work.

adding descriptions to columns in MS SQL Server 2005 with sp_addextendedproperty

rsmuckles
rsmuckles asked
on
Medium Priority
1,246 Views
Last Modified: 2012-05-06
Greetings,

I have a spreadsheet with several hundred tables and descriptions.  I have been asked to use sp_addextendedproperty to populate the metadata with these descriptions.  I have found the following suggested format but am unable to get it to work.  

I've formatted my spreadsheet so that it builds a script like the one below, but when I execute a line it reports a syntax error on line 1.

The template is first in the code below, the second part is my actual code.


USE database;
 
EXEC sp_addextendedproperty
 
         'MS_Description',
 
         'here is a nice comment about the dbo.table table',
 
         'user', dbo,
 
         'table', dbo.table
 
---------------------------------------------
 
USE BF_OPS_DM; 
EXEC sp_addextendedproperty  'MS_Description', 
'Addon table for previous version of FairQa',
'jcorey', 
dbo,
'BF_OPS_DM.dbo.Addons',
dbo.BF_OPS_DM.dbo.Addons

Open in new window

Comment
Watch Question

Commented:
Your template is for SQL 2000, you need to include a level0 for schema.  See this MSDN link.

http://msdn.microsoft.com/en-us/library/ms180047.aspx

Also, you misunderstood the template references 'user' and 'table'.  These as literals, and are telling the sp what the type is of the next parameter.  You can't substitute your values for them.

'MS_description' by the way, is an extended property from the AdventureWorks tutorial database in SQL2005 and has no significance to the sp sp_addextendedproperty, you could give your extended property any label you chose.
EXEC sp_addextendedproperty  
 
'MS_Description', 
'Addon table for previous version of FairQa',
'Schema', dbo,
'table',Addons

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
<quote>
Also, you misunderstood the template references 'user' and 'table'.  These as literals, and are telling the sp what the type is of the next parameter.  You can't substitute your values for them.
</quote>

ha, this is not surprising . . .

the code you posted is what I'm looking for, thanks for your help.

Author

Commented:
thanks again, I appreciate your help
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.