I made some interesting discoveries today. We had a file from a client’s database administrator (DBA) that had a list of all the stored procs from a certain database. He was off today, so we were unsure how he created it. Using the open source (free) editor called NotePad++, we did a “find” on “Create Proc” and it showed for example 500 stored procs. However, we knew there were about 700 stored procs on that SQL server, so where were the missing 200 stored procs?

Another client gave me a list of stored procs he created using a simple T-SQL query. So I started writing a C# program to extract the names from the first DBA, and to put them in a file so I could using another tool, Total Commander, to do a side-by-side “diff”/compare, and find the names of the missing 200 stored proc’s.

At first, I thought the DBA must have given me a human-created file, that perhaps came from their source control system. The reason was is that there so many inconsistencies, for example:

CREATE PROCEDURE ABC …
CREATE procedure ABC …
create PROC ABC
CREATE
PROC ABC …
and some had
CREATE PROCEDURE ABC AS
or
CREATE PROCEDURE ABC WITH RECOMPILE
and even
CREATEtPROCEDURE ABC (with the tab instead of space between the words “CREATE” and “PROC”)
and also
CREATE PROC ABC @PARM1 VARCHAR(30), @PARM2 INT…

Eventually, I figured out that the file did have all 700 stored proc’s in it, but it was the simple “Find/Count” command in NotePad++ wasn’t accurate enough to do the job.

So what apparently happens, is that SQL takes your exact “Create Proc” syntax, and stores in the meta-data of the database. Then when you extract it again, it has the 100% exact same format.

This is 100% opposite of the way the IDMS mainframe database works (which I used for over 20 years). In IDMS, you can say for example, “ADD REC ABC”, “ADD RECORD DEF”, “ADD RECO XYZ”, or event split it on different lines. But later, when you say “DISPLAY REC ABC”, it will spit it back out in a formal canonized syntax such like this:

{!{code}!}czo1MjpcIgogQURECiAgICBSRUNPUkQgTkFNRSBJUyBBQkMgVkVSU0lPTiAxIAogICAgZXRjLi4uIApcIjt7WyYqJl19{!{/code}!}

I wish SQL did it the same way!