SQL Code Control Made Easy

Sunday Jul 1st 2001 by Aaron Goldman

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.

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.

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 either version.

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 more help.

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 of you.

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 quite seamlessly.

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 between versions.

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.

Future Plans

I am eager to upgrade the utility and welcome suggestions from users. Email support@sqldevpro.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.

You will also need an admin book or three, but there are no outstanding ones I know of. Try this:

Thank you.

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!


Aaron Goldman
SQL Dev Pros, Inc.

Mobile Site | Full Site