Freigeben über

SYSK 93: SQL 2005 – How to Script Database One Object per File

  • Artikel

In SQL 2000, there was a feature many of us loved and used heavily – ability to script all database objects one per file.  This allowed the scripts to be stored in a source control tool for versioning purposes.  Unfortunately, this feature is missing from SQL 2005 Management Studio – the scripts are now saved in one file (not one object per file). 

 

One way to get around this limitation, is to create a database project in Visual Studio (under Other Project Types), add it to Source Control, and connect it to your database by using the wizard.  Then, in the Server Explorer, expand tables, stored procs or other categories you want to script, right mouse button click on each item and choose “Generate Create Script To Project”. 

 

Alternately, you can create a new project right in SQL Management Studio, add it to Source Control, script each object to a file by right mouse button clicking on it and choosing Script As -> Create To -> File option.  Then add the file to the project by choosing Project -> Add Existing item menu option.

 

Yes, it’s a lot of work, but once it’s done, you can maintain individual (object per file) scripts in Visual SourceSafe…  You should be able to script that, but I haven’t tried…

 

If anybody has a better/easier way to accomplish the same, don’t be shy – post your suggestion J

Comments

  • Anonymous
    May 02, 2006
    I am so not happy about this by the way.  Our entire build process is built on having, not only the individual tables scripted seperately, but the individual objects scripted seperately.

    Our dev build needs to build the table, create the indexes/keys, insert our test data, and then add fkeys.  This allows us to ensure that our test data has maintained its integrity through schema changes.

    With this new scripting, the fkeys get added when the table gets created.  If we then add data in a random order, we get fkey violation errors even thought there shouldn't be.

    I am currently searching for an add-on that some other poor soul may have created to give a bit more control over the scripting process.  If not, looks like I will be creating one.

    Thanks MS!!

  • Anonymous
    September 22, 2006
    In case anyone is looking for a utility that is able to script to different files. I found http://www.sqlteam.com/item.asp?ItemID=23185. If you don't want to get the script timestamp for each object (which would affect comparison), make sure to uncheck "Include Headers".

    Or you can do it yourself, as described in http://www.thescripts.com/forum/thread80362.html).

  • Anonymous
    September 22, 2006
    Irena,
    Just curious if you checked out the CTP for Visual Studio Team for DB Professionals.  It solves a lot of these problems and even with the bugs that are still in it seems to be a very solid product.

  • Anonymous
    May 18, 2007
    Thanks for the tip. This is excellent, I hadn't thought of it and now that you mention it makes absolute sense. Regards, Gary

  • Anonymous
    November 20, 2007
    If you apply SP2, you have the ability to script files per object.

  • Anonymous
    January 15, 2008
    Thanks. Applying SP2 does give "File per object" option in the output option screen of "Tasks ... Generate Scripts ..." wizard. Nice! Good Info.