SQL Code Control Made Easy - Page 2

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.