SQL Code Control Made Easy
July 1, 2001
I've been a SQL Server programmer for 4 years and I've attained a great deal of facility with the environment, but certain things kept bugging me, so I created a utility to fix them. This article is about the functions found in the utility that I expect you will find helpful. If you want to try the latest version, go to www.sqldevpro.com. The lite version is fully functional and FREE. As you might have guessed, I call the utility SQL Dev Pro.
In brief, SQL Dev Pro performs SourceSafe Version Control, Schema and Data Compare, Search, Export, Recompile and more for MS SQL Server.
Several of my problems centered around the scripting features in SQL Server. Being able to generate a script of objects in the database is a great feature and such a great help for making upgrade scripts and comparing code and schema versions. But I found a few problems along the way.
How do we know the procs in development are the same as in production?
The most annoying aspect of SQL programming has to be that blind spot between development and production and the nagging question "How do we know the procs in development are the same as in production?" Worse yet, how do you compare development to production to eyeball your changes before you send them out?
The first time I heard a manager ask me that question, I knew I had to do something about it. Up till now, the best option has been to extract both sets of procs to a directory, save to SourceSafe, overwrite, compare, try to remember which is which. And if you have to do it over again curse because it takes so long and is so tedious.
I could not live with this, so I set out to make this simple step by step procedure into a utility. A dozen or two hours later, I had a tool that compared stored procedures from database to database, even across servers. I also added the option of ignoring comments, case and spacing. Unfortunately, the interface only gave the answers: same, different, or can't find it.
Nifty, but not quite perfect. I had to integrate with a differencing engine to give full information. I integrated it with SourceSafe. Now at the click of a button, I can compare any two objects, tables, views, procs, triggers, between any two databases. This feature should be a must have in any SQL environment.
I said stored procedures, because I had been using ERWIN for tables, but after I got the utility working, I found it was better than ERWIN for comparing all schema objects between databases. Because it compares the current schema directly from source to destination and is not ambiguous about foreign keys, indexes, defaults, triggers, etc. I now rely on SQL Dev Pro for all object comparisons.
If you want to script objects we have this itty bitty window for you.
Part of the problem of comparing object scripts the old way, was that they were only accessible for extract in an itty bitty window in the Enterprise Manager. You couldn't just select the objects you wanted out of the hundreds likely to be there without frustrating scrolling, manual searching, clicking, checking and rechecking.
Starting from this point I resolved to make a better interface. My needs were for a multi developer project. Each developer would likely release a piece of schema independent of the others. Of course there would be many differences between development and production, but I would only be interested in the ones contained in my particular release.
Hunting around the itty bitty window for my dozen or more objects among the hundreds just seemed silly to me. I knew what I wanted and had all the object names in a table, why couldn't I just script them and compare them to production?
Of course with me in charge of the programming, now I can. With my utility, if you put your object names in a table, just run a select query and you can generate scripts for all of them in one click, no more hunting through tiny windows. You can also compare all your scripts to the production version to confirm you changes. If you want to do it again at a later date, you're just 2 clicks away from answers.
And if the script generates an error, we won't help you find where.
If that's not bad enough, if you do hit an error in your script, say something doesn't recompile, it can be impossible to tell which stored procedure didn't compile. You have an error somewhere in a twenty page script. Where? Put in some print statements and rerun it. Of course, but my opinion is that the scripter should have put them in for you.
That's what my scripter does, it scripts the drops together with the object and a print statement so you can see which object causes a problem. Even better, it does it inside a transaction so that if the script encounters an error, the whole thing will rollback.
When you're recompiling 600 procs, you usually have an interest in knowing how far you've gotten and how long it will take to finish. With my scripter printing the object name before every creation you can also judge the status of your script by watching the object names scroll by as they are being compiled. Generate the script in alphabetic order and you'll see at a glance how far your recompile has gotten.
As long as we're just recompiling why do I have to do any work at all?
Sometimes you just want to make sure your procs and triggers recompile against the current schema or refresh the sysdepends information. There should be an automated option for this. That's my opinion, and my tool backs it up. Select your database, say you want all objects, press recompile. Procs and triggers will be recompiled. You can set a pause on error or just let it rip and evaluate the results when its done.