Perl script to split sql script into separate files

Below is a quick script I put together to split one single file that contains many stored procedures into many files that contain just a single stored procedure. It can be easily modified for views, user-defined functions, etc..

Notes:
1. It does put BEGIN and END before and after the procedure creation. Because some script has grant statements in it, and I want to put END right after the procedure creation, therefore there is a variable called $FirstGrant;
2. The new files created will be named like StoredProcedureName.sql.

#!/usr/bin/perl
use strict;

open(MYDATA, "c:\\junk\\MyDb\\AllProc.sql") or 
  die("Error: cannot open file 'AllProc.sql'\n");

    my $FirstGrant = 0;
while( my $line = <mydata> ){
if ($line =~ /^create\s+proc/i) {
   close (FILE);
   $FirstGrant = 0;
   (my $filename) = $line =~ /create\s+proce?d?u?r?e?\s+\[?d?b?o?]?\.?\[?(\w+)/i;
   open (FILE, ">", "c:\\junk\\MyDb\\".$filename.".sql");
   print FILE "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[$filename]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)\n";
   print FILE "BEGIN\n";
   print FILE $line;
}
else {
if (($line =~ /^grant\s+execute/i) && ($FirstGrant == 0)) {
   print FILE "END\n";
   $FirstGrant = 1;
}
   print FILE $line;
}

}
close MYDATA;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.