Extract SQL to add or update the properties

mrekdalmrekdal Posts: 4
edited October 31, 2008 8:44AM in SQL Doc Previous Versions
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
· Share on Twitter

Comments

  • chris.buckinghamchris.buckingham Posts: 59
    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
    · Share on Twitter
  • rrussellorrussello Posts: 2
    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.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    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
    Product Manager
    Redgate Software
    · Share on Twitter
  • rrussellorrussello Posts: 2
    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!
    · Share on Twitter
Sign In or Register to comment.