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..

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.
[sourcecode language=”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 = ){
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.

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