December 12, 2006 at 12:40 pm
I'm working with a database that needs to have regular database dictionaries produced for documentation purposes. We would like to store the field descriptions in the "Description" property for each field, and have them persist on report, etc. Is there a way to easily populate this description field via an import/export process?
Right now, I have an Excel spreadsheet that the customer has typed descriptions into a column for each field name that contains the description. I'd like to import this info to the database.
Ideas?
Greg
December 12, 2006 at 1:00 pm
the data is held in the extended properties meta data - there's a set of procs which handle this data, sorry forget which and I don't have a sql server to hand to look it up - check BOL and you should find the procs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 12, 2006 at 1:28 pm
I wrote a quick and dirty Cold Fusion script that kinda-sorta does what I want:
<cfquery name="GetProperties" datasource="#sitedsn#">
SELECT table_name,column_name,description FROM Descriptions ORDER BY table_name,column_name
</cfquery>
<cfset counter=1>
<cfoutput query="GetProperties">
<cfquery name="CheckProperties" datasource="#sitedsn#">
SELECT *
FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', '#table_name#', 'column', '#column_name#')
</cfquery>
<cfif CheckProperties.recordcount eq 0>
<cftry>
<cfquery name="UpdateProperties" datasource="#sitedsn#">
exec sp_addextendedproperty 'MS_Description','#description#','user','dbo','table','#table_name#','column','#column_name#'
</cfquery>
Set #table_name# - #column_name#<br />
<cfcatch>
Error with #table_name# - #column_name#<br />
</cfcatch>
</cftry>
<cfelse>
Skipped #table_name# - #column_name#<br />
</cfif>
<cfset counter=counter+1>
</cfoutput>
<br />
Done.
I realize I could have done this in 100% SQL but this did the trick. I now have imported my descriptions from the Excel spreadsheet directly into SQL server.
Thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply