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.