How to split .sql script into multiple files?

Question

I am using DatabasePublishingWizard to create a large script creation containing both data and schema. The file that it creates is ginormous, so opening a script to fix any syntax errors is almost impossible, and for machines smaller than 4 GB it is difficult to run it! What should I do and how should I do it? Thanks to everyone for any help you can provide.

Answer 1

Using the database publishing wizard, you can create all objects created as separate files, rather than one large one. You can then put all the files in the original control and track all changes.

My current project uses a script to recreate a development database. The script deletes all existing objects and then reads them using the following statement for each object file.

sqlcmd -S% 1 -d THRIVEHQ -E -b -i "../Tables/Schema.sql" if% ERRORLEVEL% NEQ 0 goto errors

Answer 2

Just want to add Kevin's comment. Destruction of scripts in separate files. Then write a script to put all the files in the execution order.

When dropping a large database with a lot of interdependencies, since one large file will not bring you much benefit, as in most cases the script will not run without errors. In my world, I use a naming convention that helps me quickly see which representations in this case depend on other representations. For example, if I have a view that just dumps the data, I would use something like this v_VIEW_NAME_ORIGINATING-TABLE_Dump, then I would change the suffix to something like _weekly or _weekly_Summary for the views that are output from the main dump table,

I learned my lesson many years ago and have since followed this naming scheme in all of my databases.

Answer 3

Try DBSourceTools.
http://dbsourcetools.codeplex.com
It will script your entire database to disk, one file per database object.
Using deployment targets, you can recreate any database from a file.
It is specifically designed to help developers get their databases under source control.

Answer 4

Up to the head, I would say, write a script to split the file into several with a gap that occurs after each statement "GO". You can write another script to execute each broken file in order.

Answer 5

I would do it step by step.

Create all your tables and views as 1 script.

Create all your stored procedures and grants as 1 script.

Use DTS or SSIS to transfer data.

All this can be achieved using MS SQL Server Management Studio.