SQL Server 2000: search through out database

Question

Some how some records in my table are getting updated with value of xyz in a certain column. Out of hundred of stored procedures, functions, triggers, how can I determine which code is doing this action. Is there a way to search through the database some how through each and every script of the code?

Please help.

Answer 1

One approach is to check syscomments

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements..

  e.g.  select text from syscomments

If you are having trouble finding that literal string, the values could be coming from a table, or they could be being concatenated within a routine.

Try this

Select text from syscomments
where CharIndex('x', text) > 0 
and CharIndex('y', text) > 0 
and CharIndex('z', text) > 0

That might help you either find the right routine, or further indicate that the values are coming from a table.

Answer 2

This is going to be nearly impossible to do in SQL Server 2000 because the update might very well be from a variable that has that value or a join to another table that has that value and not hard-coded into the stored proc, trigger etc. The update could also be coming from a DTS package, a job, a piece of dynamic code run by the app or even from query analyzer, so the code itself may not be recorded inthe datbase anywhere.

Perhaps a better approach might be to create an audit table for the table in question and have it record the user and the code from the spid that generated the change as well as the old and new values. You'll have to wait until it happens again, but then you would know exactly what changed the value and what value to put it back to if need be.

Alternatively you could run profiler on the system until it happens but profiler tends to hurt performance and is not usually a good idea to run on a production system. If it is happening very often, it might be an acceptable alternative.

Here's a hint as to how you might get some of the info you want for the eventual trigger code you write:

create table #temp (eventtype nvarchar (1000), parameters int, eventinfo nvarchar   (4000), myspid int)

declare @myspid int
select @myspid =@@spid

insert #temp (eventtype,parameters, eventinfo)
exec ('dbcc inputbuffer (@@spid)')

update #temp set myspid = @myspid

select hostname, program_name, eventinfo from #temp t join sysprocesses s on t.myspid = s.spid WHERE spid = @myspid

Answer 3

Hi there, you might use sql-profiler to trac the update of a given table / column.

Peace & good luck Ice