Post reply

Generate Create Scripts

  • May 2, 2006 at 10:10 am

    #111943

    Microsoft seems to have removed the ability to script the individual elements of tables (fkeys, indexes, triggers, etc) into their own files.  This is causing problems in my dev environment where not only do we like to keep each of these scripts in seperate directories so that they can be looked and modified as needed, it is also an important part of our process.  When we build our dev environment we import data from DAT files after adding the indexes (so that pkey constraints can be verified) but before we apply fkeys (in order to verify that the data has maintained its integrity and still follows the fkey constraints).  This new model that they have implemented doesn't allow this because it appears that at the lowest level I can get one file for all of the DDL for the table.  This makes it so that fkey constraints are applied before data is in the tables.  Does anybody know if there is an add-in for VS2005 that will allow us to get this higher granularity in our scripts?  Or perhaps another way to build a dev environment that will allow us to verify that all of the constraints are still valid with our test data?

    Thanks

  • May 2, 2006 at 10:42 am

    #635377

    Hello Tim,

    You can create the scripts by right clicking on the database and choosing tasks -> generate scripts. here you need to set either true or false based on your requirement.

     


    Lucky

  • May 2, 2006 at 10:54 am

    #635385

    Thanks for the reply Lucky.

    I see that we are still able to script the database and have control over what is scripted.  The problem is, that first, if you choose multiple tables, everything gets scripted into a single file.  If you choose an individual table, everything for the table gets scripted into a single file. 

    I probably should have been more clear.  The functionality that I am looking for was much more robust in Visual Studio 2003 Enterprise Edition where it allowed me to script a single table into files such as table.tab, table.trg, table.fky, table.kci .  Neither VS2005 nor SQL2k5 Management Studio seem to allow this without going through every table multiple times in order to get the individual files (which would be an absolutely horrible experience for anybody with more than 5 tables in their database).

    Furthermore, another thing that I noticed, is that there also doesn't seem to be a way to export data into dat files any more.  This was pretty important for dev builds that need to be dropped and recreated quite often with different data as requirements change.  Am I missing something regarding the tools that I have chosen to use?

  • May 3, 2006 at 7:29 am

    #635590

    Can you leverage SSIS and the ForEach container to loop through each table in a database and generate a script?

  • May 3, 2006 at 7:58 am

    #635609

    I started using Scriptio a few weeks ago. It is a .net app that you can get for free from http://www.sqlteam.com/publish/scriptio. The main reason why I use it is (a) it scripts to individual files and (b) very few clicks are needed to re-generate my scripts. Try it and see if it helps. The only problem that I have found is that I need to ensure my .sql files are not read-only (CVS is cfgd that way for us) before generating the schema scripts. Scriptio rocks!!

  • May 3, 2006 at 8:03 am

    #635621

    Great minds think alike.

    K. Brian Kelley
    @kbriankelley

  • May 3, 2006 at 8:03 am

    #635620

    Take a look at Scriptio from Microsoft MVP Bill Graziano. It does the job quite nicely. You will the .NET Framework 2.0 installed.

    K. Brian Kelley
    @kbriankelley

  • May 3, 2006 at 9:29 am

    #635683

    Ok.  So I have two people saying Scriptio.  So... I'm trying Scriptio.

    Thanks

  • August 31, 2006 at 9:23 am

    #657849

    I'm using the SQL2005 Management Studio for my 2000 databases, but Scriptio is only for 2005 databases. Any other ideas for scripting out individual files ?  Currently, I go back to Enterprise Manager when I need to do this.  Any I dea why Microsoft removes functionality such as this and other things ? 

  • August 31, 2006 at 9:29 am

    #657854

    The interesting thing about this is that as long as I have the client tools for 2000 on my machine, I can use VS2003 Enterprise Ed to script my 2005 database the way I need (not to mention create the data files for the tables). 

    So that leaves me with (after Microsofts grand unification of the different tools for simplification):  Management Studio, VS2005, and VS2003 to do the work.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply