?
Solved

adding descriptions to columns in MS SQL Server 2005 with sp_addextendedproperty

Posted on 2009-02-09
3
Medium Priority
?
1,181 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

0
Comment
Question by:rsmuckles
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
folderol earned 2000 total points
ID: 23592228
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

0
 

Author Comment

by:rsmuckles
ID: 23592649
<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.
0
 

Author Closing Comment

by:rsmuckles
ID: 31544533
thanks again, I appreciate your help
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question