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