Extract SQL to add or update the properties

Documents SQL Server 2000, 2005 and 2008 databases.


Moderators: David Atkinson, david connell, Charles Brown

Extract SQL to add or update the properties

Postby mrekdal » Sat Mar 08, 2008 11:21 pm

I would like to do documentation on one database, and then reapply the same documentation to another instance, i.e. add the extended properties there as well. Is that possible, without a lot of manual cut and paste?
Magne Rekdal, MD
Consultant, Norwegian National Diabetes Register
Managing Director, Emetra AS
mrekdal
 
Posts: 2
Joined: Sat Mar 08, 2008 11:16 pm

Postby chris.buckingham » Tue Mar 11, 2008 3:47 pm

The best way I think to do this is after you have made your changes to Database A, run a SQL Compare between Database A and an empty database. You will obtain a synchronisation SQL script which you can extract and process - you can run through this script extracting all the lines containing 'sp_addextendedproperty' and insert them to another SQL script (you could write a simple VB application to do this for instance, or use a Perl command). This SQL script in SSMS would then write the extended properties onto Database A for you.

Running SQL Compare then between Database A and Database B would obtain another SQL script that you could process in a similar way, and run that against Database B, and synchronise them.

The alternative would be to write a Toolkit application, extracting and running only the lines containing 'sp_addextendedproperty'
Chris Buckingham
Red-Gate support
chris.buckingham
 
Posts: 56
Joined: Mon Jul 30, 2007 1:39 pm

Extract SQL to add or update the properties

Postby rrussello » Thu Oct 30, 2008 8:44 pm

I would also like to see an 'export' feature added to a future release to SQL DOC. This was a deciding factor on my company's purchase of a SQL Documentation tool. Since no other tools offer this feature, and we already have a license for SQL Compare we decided to stay within the RedGate Family. We would also like to have documentation on one database and reapply it to another database with the same schema.
rrussello
 
Posts: 2
Joined: Thu Oct 30, 2008 8:34 pm

Postby David Atkinson » Thu Oct 30, 2008 11:53 pm

Hi,

Have you considered using SQL Compare to transfer the object descriptions across? They are just extended properties so will be identified as differences when you compare the two schemas, as long as you make sure you haven't ignore extended properties in the options.

David Atkinson
Red Gate Software
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby rrussello » Fri Oct 31, 2008 1:44 pm

Yes I have used SQL Compare and it works very well for getting a script to update a live production database. We also are using the initial script to add the extended properties into a new database for a customer. Using SQL Compare generates a script of about 2000 lines that we trigger upon creation of the new database. If our schema changes this script will fail, and the new database would not contain the documentation. If SQL Doc had a feature to export the extended properties we would not have the problem of maintaining an initial script upon a new database creation. Thanks!
rrussello
 
Posts: 2
Joined: Thu Oct 30, 2008 8:34 pm


Return to SQL Doc 1

Who is online

Users browsing this forum: No registered users and 0 guests