SQL Server 2005: Scripting each objects to a separate file

Recently, I decided to set-up a central repository for all our source codes, packages, documents and GIS maps using the open-source Subversion (See my post on this). So after successfully installing the SVN Server, my next task was to commit all SQL scripts to /svn/scripts directory of our repository. And at the moment I thought, how should I generate all the sql scripts in their own file? Before, I was used to just generating sql scripts (tables, stored procedures, views, udf’s) into a single file on our SQL Server 2000 but haven’t tried separating each script to a file.

Not knowing yet what features holds for SQL Server 2005 Management Studio, I immediately look into using EMS products and ApexSQL Script. The EMS doesn’t have the feature to extract all objects into separate files (not that I’m aware of). And for ApexSQL Script, they do have this feature but we don’t have this software but wonderful product indeed based on the listed features on their website.

I googled and it linked my query to Microsoft site What’s New in SQL Server 2005 SP2.

It states under Scripting:
Generate Script Wizard. You can now specify that the scripted objects include a DROP statement before the CREATE statement.  Scripting of objects into separate files is now possible. This feature is supported in SQL Server 2005 Express Edition SP2.

Whoa! What a relief! The SP2 version is now support this feature, so I immediately run and check to see if my SQLExpress is on SP2:

   1: select serverproperty('productlevel')

The query returns SP2.

I then fired-up Management Studio, connect to my existing database and click the Generate scripts submenu:

On the next window, Select Database appears, if you’re like me that needs to generate all existing scripts on my database, you can check to enable Script all objects in the selected database. As seen on the red circled:

On the Script Options Window, I set the circled items to False coz I don’t need them enabled and appear on all my scripts.

After clicking Next, the Output Options Window displayed, this is the window that allows you to select the files to generate, either by Single file or File per object. I selected the latter coz this is what I wanted and browse my folder to a location on which the generated files will be placed. On the Save As option, I selected the ANSI text for the reason that the produced .sql file will be treated as text/plain and not as a mime-type: binary object of TortoiseSVN client. This will allow us to view the contents of our SQL script source codes via WebSVN (See separate post on how I install WebSVN on both Windows 2003 Server and Ubuntu 7.10 Server) and not to download as default behavior for binary objects.

If your file will be uploaded to your SVN repository and to be viewed online, I suggest reading this post (to be posted soon) to format binary to plain text.

Back to SQL window, clicking Next or Finish will give you the Script Wizard Summary. On this window, you can review the items that the Script Wizard will do.

Click Finish and generation of scripts will commence.

The generated files will look similar like this:

The filename that was created is the actual name of the SQL object followed by the object type (User, View, Table, Stored Procedure, Schema, User Defined Function and Role) to easily identify what contents the file will have.

If you’re like me and like to remove the type added by SQL Script on the file generated, please read my post (to be posted soon) )on how I removed those extras by doing a batch rename trick.