SQL : Comparing VSS stored procedures with the DB

This is a right royal pain. You have to extract every .pkg and .pkb manually e.g. if using SQL Navigator you right click, Extract DDL and save.

But how do you ensure that VSS is up-to-date?

An easy way is to right click on the heading e.g. right click on Package Bodies and then Extract DDL from here. What you are doing is creating one DDL file that contains ALL the package stored procedures.

In VSS, however, each package is stored individually. To combine them, DOS comes to the rescue! Use the copy construct "+".

e.g. copy a + b + c d.

This concatenates a, b and c and puts them in a file called d.

Now you can compare the combined DDL extract with the concatenated file from VSS.

But wait, there's more!

Part of the DDL extract puts the date on the extract and the tnsname that you use to access the DB. Different developers may have different tns names. So you need to remove these lines. This can be done very simply by writing a program to parse the file and create a new one without these lines.

e.g. in C#, something like:

(where the command line is something like 'Program 'input file' 'filtered file'')

static void Main(string[] args)
{

string readBuffer = "";
int gIndex = 0;
int fIndex = 0;

if (args.Length != 2)
{

Console.WriteLine ("\nFormat is Prog 'input' 'output'");
System.Environment.Exit (0);

}

try
{

FileStream readStream = new FileStream (args[0], FileMode.Open);
StreamReader fileRead = new StreamReader (readStream);
FileStream writeStream = new FileStream (args[1], FileMode.Create);
StreamWriter fileWrite = new StreamWriter(writeStream);

while (fileRead.Peek () >= 0)
{

readBuffer = fileRead.ReadLine();

gIndex = readBuffer.IndexOf ("-- Generated");

if (gIndex == -1)
{

string tempReadBuffer = readBuffer.ToLower();
fIndex = tempReadBuffer.IndexOf ("-- from 'a specific DB string'");

if (fIndex == -1)
fileWrite.WriteLine (readBuffer);

}

}

fileRead.Close ();
fileWrite.Flush ();
fileWrite.Close ();

}

catch (Exception e)
{

Console.WriteLine("Error : {0}", e.ToString());

}

}

So the batch file would be:

copy concatenate statement
run filter on concatenated file
run filter on saved DDL file
compare the two filtered files

You could use something like Examdiff which is free to do the compare in which case the batch file line would be:

start ExamDiff.exe File1 File2

Enjoy!