Aaron Goldman introduces you to his SQL tool with multiple utility functions, including version control, schema and data compare, search, export, recompile and more for MS SQL Server.
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
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
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
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
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.
Where's my version history?
Though I don't often use it, code history in SourceSafe can be a godsend when
you get yourself in a little trouble. The last time I checked the
Microsoft solution for using SourceSafe with SQL Server it was nasty to set up
if you could at all and it only worked for stored procedures (maybe triggers
too--its been quite a while), but not tables and views. And if anyone
modifies an object outside of Visual Interdev, it doesn't get captured.
Since I already had the SourceSafe integration to look at differences, I
figured wouldn't it be great if I could archive my code to SourceSafe. So
that's what I did and my tool transmogrified into an archiving solution. Pick
your databases, pick your SourceSafe projects, press go. Viola, SourceSafe
that works easily for all basic object types.
Where's the modification date?
Every so often (every morning) I start my day and want to pick up on some
things I was working on yesterday. In SQL Server this is quite difficult
for me since I am quite forgetful of details ( I prefer the conceptual) and I
generally work in databases with hundreds of objects. So I have a hard
time remembering the names of the objects I was working on yesterday (OK an hour
ago). SQL Server's habit of keeping create dates and not modification
dates makes it extra hard. At least in Version 6.5 a proc was always
dropped before being changed (so you could just look for procs created
recently), now in version 7.0, if you're using alter proc, you can't find your
proc by create date. Forget about finding the date of a modified table in
Though it didn't really spring from anything else I had done, I wanted
this feature. Searching the catalog schema I found it was possible. Now,
when I'm looking for my work, I just click my dropdown list of objects ordered
by modified time and search from latest to oldest. Its also a good way to see
what the other team members are working on, because their latest work is at the
top of the list too.
But I still can't find my object!
Well, modification dates are great, but finding an object often takes more
work for me. Sometimes other team members have recently modified so many
objects that the list for the past couple days is too long to
digest. Other times, I'm looking for an object I made a while ago and I need
I made a search interface that uses pattern matching. Using a SQL pattern
string, if I know a piece of the object's name, I can find it. Better yet, if
its a proc or trigger and I know the table it references or some unusual words
in the text, I can search all the SQL text for a match. When I'm looking for a
proc that uses the userPreference table and I know the proc name has the word
'preference' in it, I can enter '%pref%' and search for object names like
that. Or if I don't know the name of the proc at all, I can search all the
SQL text in the database for '%userPreference%' and get a short list of
procedures, triggers, and views that reference that table. I can now
usually quickly find what I'm looking for.
How can I find references to an object?
This search of the SQL text in the database can also come in handy if you're
looking for code that uses particular tables, which can be quite often.
Sure, you can go to sysDepends, but remember, that's not accurate if you haven't
recompiled lately (which my utility will also do for you BTW). Also, it
won't help you if you're trying to find references to a column name. If
your column names are more or less unique within your database, you can find all
references to the column name quickly.
Now that I know the name of my object, I only have to page through 600
objects to edit it.
Sure, I can help you find the names of objects you're looking for, but then
what? You want to edit them, and now you have to go into another
environment and search through there again for the name you have right in front
To me this seems to be a deficiency of windows itself. Whenever you're in an
explorer type interface, you can type a few letters to get where you want to go.
In a treeview, as found in Visual Interdev and many other popular SQL
development environments, you can do the same thing. But you must type the name,
having it on the clipboard doesn't help.
A solution - A new hotkey!
Now you can automatically 'type' the text on the clipboard. If you copy the
name of the object you want from my tool and select the appropriate starting
place in your tool, my hotkey (WindowsKey-T by default) will jump right to your
selection. This works in Visual Interdev, SourceSafe, Windows Explorer or any
other similar interface.
My tool gives you another option as well, you can choose to edit the object
right from my interface and my tool will script the object(s), save it to a file
with a '.SQL' extension and bring up your default editor. If your editor is
Visual Interdev or Query Analyzer (nee ISQL/w) you can find, edit and recompile
The hotkey can also help you when you're in your standard development
environment (mine is Visual Interdev). If you have a lot of procs referencing
other procs or you want to examine a referenced table, all you have to do is
copy the name to the clipboard, highlight the object list, and press the
hotkey. You will jump right to your target.
I just want this object from here to there.
Good change control requires a documented script for upgrades, but if you
have several development and test databases and need to move objects around, I
can do it for you. Sure you can do it in enterprise manager (beware SQL
7's nasty habit of turning tabs into single spaces), but EM won't difference
your objects beforehand, and you'll have to deal with some itty bitty
windows. It also won't search by date or pattern for you either. Suffice
it to say, I use my tool and so does everyone else who has tried it. (It
won't move the data for you, but that feature is planned.)
Why can't the same tool work for both the 6.5 and 7.0 version?
This is one of the few tools that will work with versions 6.5 and 7.0
simultaneously. There are no switches to set to change between versions,
just point and shoot. You can even compare scripts and import and export
I rebuilt and rearchitected this SQL Dev Pro to work with both
versions. I certainly hope people find it worth the effort. Going forward,
I expect to incorporate more and more SQL 7 only features, but the core will
remain dual compatible.
Data Compare and Export
SQL Dev Pro will compare the data between tables and show you
differences. The data import/export function is a planned upgrade.
I am eager to upgrade the utility and welcome suggestions from users.
Email email@example.com with any
comments or suggestions.
Current plans call for data import/export functionality, server and database
settings comparison and monitoring, job import export and sourcesafing, more complete
documentation, Visual Interdev Integration, and multi developer/project coordination with object groups.
People are always asking about SQL development environments and training
issues so I thought I'd voice my opinions here.
What is a truly great SQL development environment?
Visual Interdev Interdev is my unreserved recommendation. Though it lost VBA
programming and strangely its outdent going from version 1.0 to version 6.0, its
my favorite. Its code editor is quite good, and table editor is great. Its
models are great for seeing and editing a few tables at once, but it is not a
good modeling tool.
Other good tools?
Erwin is a great modeling and documentation tool, but it is so pricey and my
friends tell me that CA (which bought Platinum which bough Logic Works -- I keep
thinking of big fish eating little fish) is a killer of great software. Erwin's
strength is in modeling from concept to initial design. It is the best way
to make schema when you're making a bunch of tables at once. After that, I
prefer Visual Interdev for adding a table or column one at a time. But for
documenting, and making multiple views of the database for easier understanding
I keep using Erwin.
SQL Dev Pro is the topic of this essay, and many users find it
indispensable for differencing schema and data, scripting, SourceSafe archiving,
searching, export, and recompile.
What are the good books?
I've seen Joe Celko referred to as a SQL God. If you want to be an
advanced SQL programmer, SQL for
Smarties, is my top recommendation.
If you want to know how SQL Server works internally, and if you're working
with SQL Server you should, read Inside SQL
Server 7.0 (or 6.5) by
Ron Soukup and Kalen Delaney.
For an introduction to front end programming read Hitchhiker's
Guide to Visual Basic and SQL Server by William R. Vaughn.
If you've read or scanned this far, thanks for your interest in SQL Dev Pro.
I hope you like it. Check out the software at www.sqldevpro.com - the fully functional
lite version is FREE!
SQL Dev Pros, Inc.