none
Order of objects in SQL scripts from Generate Script wizard RRS feed

  • Question

  •  

    The Tasks->Generate Script action in SQL Management Studio does not seem to produce objects in a repeatable order. I recall in SQL 2000 I would generate a single file SQL script for all my database objects, then use Text comparsion tools to compare versions of the schema. This allowed me to easily compare changes between versions of the database.

     

    Others have suggested similar practices such as :Comparing Two SQL Server Databases"
    http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=210

     

    My problem is that in SQL 2005, the objects in the generated scripts do not appear to be in any specific or repeatable order, it is not practical (or even possible) to use text comparsion tools to verify schema changes.

     

    Is there a way to get SQL Management Studio to generate scripts for objects in a repeatable order?

    Monday, March 10, 2008 1:48 PM

Answers

  • If you feel that this is a bug that should be corrected, or you want to make a suggestion to Microsoft, you can submit it at

     

    SQL Connect Site

     

    You can also investigate using Visual Studio Team Edition for Database Professionals.  I don't do source management in the method that you are trying to do it, so the fact that this doesn't script out in logic object order has never bothered me to much.  I don't know if that is truely a bug or how it was intended to function.

     

    If you search online, you can find some VBScripts that use SMO and DMO to script out objects.  I haven't tried any of them yet, so I can't make a recommendation on any of them.  I also haven't found something that specifically does it in dependency order, but I am testing some code I wrote to see if I can do it.  If I find an answer I will post it back here for use as an interim solution for you.

     

    Monday, March 10, 2008 7:04 PM
    Moderator

All replies

  • Take a look at the Database Publishing Wizard.  It should simplify what you are trying to do.

    Monday, March 10, 2008 3:53 PM
    Moderator
  • Thanks for the suggestion about SQL publishing wizard, but this doesn't do what I need.

     

    The idea is I want to generate scripts of my database so I can:

    1. Check those scirpts into source control

    2. Compare versions of the scripts in source control using a text Diff program (I like beyondCompare)

    3. Use the SQL generate script wizard to capture the current state of a devleopment or production database so it can then be compared to database scripts in source control to determine if any changes have been made.

     

     

    The SQL publishing wizard generates scripts that include "Descriptive Headers". Therefore, each object in the generated script includes a comment with the date and time the script was generated. This makes it more difficult to use a text compare program to compare two script snapshots of a databsae.

     

    Also, the SQL pub wizard seems to use the same underlying Generate Script wizard which does not seem to generate objects in a repeatable order. In order to make these scripts useful for comparing database versions and identifying changes, it is really necessary for script generation tools to output their object defintions in a repeatable or consistent order.

     

     

     

    Monday, March 10, 2008 4:11 PM
  • I'm not alone with this concern. http://www.codeplex.com/ExportSQLScript says:

     

    SQL Server Management Studio's "Generate SQL Server Scripts Wizard" appeared to be the right tool for the job but:

    • After fulfilling dependencies, objects were created in no particular order (could change between DB instances)

     

     

    Monday, March 10, 2008 6:44 PM
  • If you feel that this is a bug that should be corrected, or you want to make a suggestion to Microsoft, you can submit it at

     

    SQL Connect Site

     

    You can also investigate using Visual Studio Team Edition for Database Professionals.  I don't do source management in the method that you are trying to do it, so the fact that this doesn't script out in logic object order has never bothered me to much.  I don't know if that is truely a bug or how it was intended to function.

     

    If you search online, you can find some VBScripts that use SMO and DMO to script out objects.  I haven't tried any of them yet, so I can't make a recommendation on any of them.  I also haven't found something that specifically does it in dependency order, but I am testing some code I wrote to see if I can do it.  If I find an answer I will post it back here for use as an interim solution for you.

     

    Monday, March 10, 2008 7:04 PM
    Moderator
  • If you want to try this out, it looks like it should do what you are after.

     

    http://www.codeplex.com/ExportSQLScript/SourceControl/DownloadSourceCode.aspx?changeSetId=4665

     

     

    Monday, March 10, 2008 10:12 PM
    Moderator
  • Jonathan, thanks again for your response. Yes, I've seen that tool. I linked to it earlier in this thread at "10 Mar 2008, 6:44 PM UTC".

     

    I will look into it, but since ExportSQLScript is a community supported open source project with only one contributor and it is still considered Beta, I would prefer a supported Microsoft product solution.

    Wednesday, March 12, 2008 1:36 AM
  • Unfortunately there doesn't seem to be one other than Visual Studio Team Edition.  Hopefully when you get a response to your connect posting, the SQL team will provide further information on this.  Your only option is to find some sort of work around for this in the mean time, or purchase one of the other tools out there that does what you are asking for, and has vendor backed support.  Idera has one, as does RedGate.  It doesn't seem like you want to pay for this, so the only thing you can do is wait for a response from your Connect posting.  If you find something, or receive an answer to your Connect posting, please update this posting so that others in your perdicament can benefit from your headache.  That isn't much consolation, but it does a lot to keep the online community up on what is going on.

     

    Wednesday, March 12, 2008 1:49 AM
    Moderator
  • My work around for this problem is to parse the result file from the wizard and create individual files for each statements for table, procedures, views, etc.  This will eliminate the ordering dependency and I can use a compare tool that will compare the differences between files. This also allow me to locate the exact script I need without scanning the big file.

     

     

    Friday, April 18, 2008 1:09 AM
  • That is a good workaround too, though I didn't want to manage so many individual files.

     

    By the way, the Script Wizard can generate separate files for each object. On the Wizard's Output OPtion page, you can select 'Script to file' and Files to Generate 'File per object'

    Friday, April 18, 2008 1:59 PM
  • For SQL Source code management, I would recommend you to use Visual Studio 2010 Database projects for SQL Server 2005/2008 and Visual Studio 2012 Database projects for SQL Azure/SQL Server 2012. Do let me know if you need any help to convert your existing database to Visual Studio 2010/2012 Database projects.

    Tuesday, December 18, 2012 7:11 AM
  • Opened a new issue as old one closed in error.

    https://connect.microsoft.com/SQLServer/feedback/details/2762431

    Monday, May 30, 2016 2:56 AM