Click here to Skip to main content
Click here to Skip to main content
Go to top

Split T-SQL Script Into One File Per Object (SQL Server 2005)

, 14 Aug 2009
Rate this:
Please Sign up or sign in to vote.
Creates one file per object based on split and name criteria

Introduction

This is a console application that splits a script based on a "split expression". It tries to compensate for the lost feature from SQL Server 2000 which allowed one file per object when generating a database script.

Background

After creating all of the stored procedures for the project I was in, it was time to put them all on source control. It should have been done earlier, but that's not the point here.
I was assured that SQL Server 2005, as in the previous version, had a "Create one file per object" option. I went searching the net and found out that the feature was missing. Another search and I read that the missing feature appears when updating SQL Server 2005 client tools to Service Pack 2, but the code I post here was already written.

How It Works

After generating a stored procedure script (or any other) to a single file, certain common "headers" can be found.

Usually, something like this:

SET ANSI_NULLS ON
GO 
SET QUOTED_IDENTIFIER ON
GO

So, we can use "SET ANSI_NULLS ON" as a mark to create a new file for this object.

To assign a more intuitive name for each file, the argument 'textContainingFileName' is used to identify where to look for the file name. The place is inside the expression where the file name should be is tagged with #filename#.

For something like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[GetUser]
(...)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[GetProfile]
(...)

The 'splitExpression' will be "SET ANSI_NULLS ON" and 'textContainingFileName' "create PROCEDURE [dbo].[#filename#]"

Usage:

ScriptSplitter sourceFilePath splitExpression textContainingFileName [\e[fileExtension]] 

Example:

ScriptSplitter c:\StoreProcedureScript.sql "SET ANSI_NULLS ON" 
			"create PROCEDURE [dbo].[#FILENAME#]" \e sql   

The code was designed with T-SQL in mind, but it should work on any text file with similar scheme.

Below is the method Split, which does almost all the work:

static void Split(string sourceFilePath, string splitExpression, 
		string textContainingFileName, string fileExtension)
        {
            fileExtension = GetExtension(fileExtension);
            splitExpression = splitExpression.ToLower();
            textContainingFileName = textContainingFileName.ToLower();
            textContainingFileName = ReplaceRegExMetaChars(textContainingFileName);
            
            FileInfo fi = new FileInfo(sourceFilePath);            

            Regex fileName = new Regex(textContainingFileName.Replace
			(fileNameTag, "(.*)"), RegexOptions.IgnoreCase);

            StreamReader sr = new StreamReader(sourceFilePath);           
            String str;
            String newFileName = string.Empty;
            StringBuilder sb = new StringBuilder();
            int count = 0;
            while (!sr.EndOfStream)
            {
                str = sr.ReadLine();
                if (str == null) continue;

                if (str.ToLower().Contains(splitExpression))
                {                    
                    NewFile(fileExtension, fi, newFileName, sb);
                    newFileName = String.Empty;
                    sb = new StringBuilder();
                    count++;
                }

                if (fileName.IsMatch(str))
                {
                    newFileName = fileName.Matches(str)[0].Groups[1].ToString();
                }

                sb.AppendLine(str);
            }
            sr.Close();
            Console.WriteLine(count.ToString() + " Files created");
        } 

Since the match of 'textContainingFileName' is made using Regular Expressions, the RegEx meta characters (i.e. '.[]()', etc.) have to be escaped. After that, the #fileName# tag is replaced with RegEx (.*) which matches an expression inside a match. In our case, the expression indicates any character any number of times.

Using the previous example, our matching expression will be "create PROCEDURE [dbo].[GetUser]" with a sub-match (group) "GetUser".

When a 'splitExpression' is found, the content of the stringBuilder object is dumped to a new file named after the regEx match. If no match is made, a GUID is used to name the file.

History

  • 1.0.1 (14 Aug 2009)
    • Fixed file extension
    • Fixed char '*' in expression
  • 1.0 (09 Feb 2009)
    • Initial release

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Share

About the Author

João Maçãs
Software Developer
Portugal Portugal
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMarcelo Rossi27-Jul-12 11:59 
GeneralUseful ! PinmemberFederico Colombo1-Jul-10 8:02 
GeneralPerfect! Pinmembercplas17-Feb-09 17:31 
GeneralRe: Perfect! PinmemberJoão Maçãs1-Mar-09 23:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140905.1 | Last Updated 14 Aug 2009
Article Copyright 2009 by João Maçãs
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid