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

• Download demo - 3.47 KB
• Download source - 3.68 KB

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