Introduction
MS SQL allows exporting database objects into sql script file (for instance: stored procedures with the body). ( Database -> Tasks->Generate Scripts) This option allows creating one single script file for all objects exported or separate file for every object.
But what to do if we have one single file with all stored procedures without an access to MS SQL. Or if we want pure SP body not enriched by additional stuff created by "Generate Scripts".
Sometimes we have two versions of the script file (earlier and later) and want to compare what was developed in the meantime. It is much easier to compare having every SP in different file, we can use a standard comparing software (like
Meld).
Facing this problem I created a simple Python script which extracts all SP from a single script file and put them into separate files.
Python code - Extract class
'''
Created on 2 lip 2014
@author: sbartkowski
'''
import logging
import os
import shutil
LOGNAME="EXTRACT"
_L = logging.getLogger(LOGNAME)
_CREATE="CREATE"
_PROCEDURE="PROCEDURE"
_GO="GO"
class EXTRACT() :
'''
Extracts SP from single export file. Every SP extracted is placed in a separate file.
It is assumed that the body of the SP is enclosed by CREATE PROCEDURE and GO statements.
The file naming convention is : schema_SP name.sql
Example:
CREATE PROCEDURE [sales].[SelectUnsold]
....
GO
file name: sales_SelectUnsold.sql
The output directory is created or cleaned if exists
Attributes:
infile : the name of the input file containg a bunch of SP
outdir : output directory
f : 'file' object' opened as 'infile'
'''
def __init__(self, infile,outdir):
'''
Constructor
Args:
infile : input file
outdir : output directory
'''
self.infile = infile
self.outdir = outdir
self.f = None
def _isProc(self,l):
'''
Test if the line contains CREATE PROCEDURE
Args:
l : source line
Returns:
True : if CREATE PROCEDURE
False: otherwise
'''
tokens = l.split()
if len(tokens) < 3 : return None
if tokens[0].upper() != _CREATE or tokens[1].upper() != _PROCEDURE : return None
return tokens[2]
def _isClosingGo(self,l):
'''
Test if the line contains GO statement (denoting the end of SP)
Args:
l : source line
Returns:
True: line with GO
False: otherwise
'''
tokens = l.upper().split()
if len(tokens) != 1 : return False
return tokens[0] == _GO
def _outFileName(self,procName) :
'''
Constructs the name of the output file related to the SP name
Args:
SP name in shape of [schema name].[proc name]
Returns:
The output file name (without path)
'''
pName = procName.replace("[","").replace("]","").replace(".","_")
outFileName = os.path.join(self.outdir,pName) + ".sql"
return outFileName
def _writeFile(self,fileName,listofl):
'''
Flushes the body of the SP to the output file
Args:
fileName : the name of the output file
listofl : the list of lines with SP body
'''
f = open(fileName,"w")
f.writelines(listofl)
f.close()
def run(self):
'''
The main method, bounding them all
'''
# print self.infile, self.outdir
_L.info("Start executing")
_L.info("Source : " + self.infile)
_L.info("Output dir :" + self.outdir)
if os.path.exists(self.outdir):
_L.info("Removing content of " + self.outdir)
shutil.rmtree(self.outdir)
_L.info("Creating directory " + self.outdir)
os.mkdir(self.outdir)
_L.info("Opening source file")
self.f = open(self.infile, 'r')
_L.info("Success, opened")
listofl = None
procName = None
noprocs = 0
nolines = 0
# read line by line
for l in self.f :
if listofl == None :
procName = self._isProc(l)
if procName :
_L.info(procName)
listofl = [l,]
noprocs = noprocs + 1
continue
else :
listofl.append(l)
if self._isClosingGo(l) :
_L.info("End of " + procName + " found")
outfileName = self._outFileName(procName)
nolines = nolines + len(listofl)
_L.info("Creating " + outfileName + " lines:" + str(len(listofl)))
self._writeFile(outfileName,listofl)
listofl = None
_L.info("Closing source file")
_L.info(str(noprocs) + " SP extracted with " + str(nolines) + " lines of code")
self.f.close()
Python code - main
Below is an example of usage of this files. It reads two scripts file containing SP and put them into two directories, "old" and "new"
'''
Created on 2 lip 2014
@author: sbartkowski
'''
import logging
from extr import extr
def setLogger():
logger = logging.getLogger(extr.LOGNAME)
logger.setLevel(logging.INFO)
ch = logging.StreamHandler()
ch.setLevel(logging.INFO)
#formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
#ch.setFormatter(formatter)
logger.addHandler(ch)
INDIR1="/home/sbartkowski/Dokumenty/db2/SP/2014-03-22"
INDIR2="/home/sbartkowski/Dokumenty/db2/SP/2014-06-22"
OUTDIR1="/home/sbartkowski/Dokumenty/db2/SP/old"
OUTDIR2="/home/sbartkowski/Dokumenty/db2/SP/new"
INFILE="SP.sql"
def main() :
setLogger()
E = extr.EXTRACT(INDIR1+INFILE,OUTDIR1)
E.run()
E = extr.EXTRACT(INDIR2+INFILE,OUTDIR2)
E.run()
if __name__ == '__main__':
main()
