How to Search a Database? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to Search a Database?

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2010-01-18 : 16:00:13

I have the following field in an Employee table:

int DISPODR

There are 32 distinct values for the roughly 2000 entries:


NULL, 0,
100, 101, 102, 103, 104,
201, 202, 203,
300, 301, 302, 303, 305,
400, 401, 402,
500, 501, 502,
600, 601, 602, 603,
702, 703, 704, 706, 707,
801, and 802

Though no one at our company seems to know what this field is for, it accurately ensures that Managers and Supervisors display at the top of our Crew List Report.

The problem is that the Crew List was written in VB over 10 years ago and the source code is gone!

I am tasked with re-creating it.

I want to search our database to see if some stored procedure or view creates or modifies this field, but I don't know how to search the old database. I have tried going into each of the hundreds of procedures and views, but my eyes lose focus quickly.

Is there any way to determine what is setting this value?

Regards,
~Joe

Avoid Sears Home Improvement

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-18 : 17:08:02

[code]SELECT o.name
FROM sysobjects o
JOIN syscomments c
On c.id = o.id
WHERE type = 'p'
And text like '%your table name here%'[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-18 : 17:11:12

First you maybe have the chance to find a screen in your old VB application where you can store and change data for the Employee table. In this screen look out for a field called 'display order'.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 02:24:46

You could use Profiler to "watch" the SQL that the VB app sent to the database.

As well as searching SProcs for the table / column name you could also use Profiler to watch for any dynamic SQL that contained it. Bit like looking for a needle-in-a-haystack though.

Or you could do a scream test

Rename the column and wait for the screams from users! then fix the software, bit by bit, as you find it.

Once you think you've found the culprit(s) you could rename the table (e.g. MyTable_V2), get all apps to use the new V2 table, and create a backwards-compatibility view, with the original table name, just in case anything else comes along once-in-a-blue=moon.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-19 : 04:07:10

to put my oar in:

If you want to emulate what the vb application is doing when it modifies that column you could put a trigger on the table to copy the old value to a holding location. then you could compare before and after and check what happens when you do specific things.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources