Microsoft SQL Server forum - Any way to find out which SP is updating data in a specific table?

Author

Any way to find out which SP is updating data in a specific table?

serge

2005-09-21, 8:23 pm

Can you create an UPDATE TRIGGER and use some type
of code to figure out which SP just updated the current table?

If not how can i achieve what i want?

I tried to run SQL Profiler and i don't understand why i can't
simply have the Profiler filter events only for the specific database id
and the table's object id i chose?

What am i doing wrong with SQL Profiler? I was testing this
through SQL EM. I had the filters chosen for a specific database id
and a specific table's object id, yet when i open another table SQL
Profiler captures that information too.

Thank you

--CELKO--

2005-09-22, 3:23 am

In a correct design, why would it matter? The event or conditions
rather than the agent should be what is important. Do not think in
terms of HOW, but in terms of WHAT.

Erland Sommarskog

2005-09-22, 8:24 pm

serge (sergea@nospam.ehmail.com) writes:
> Can you create an UPDATE TRIGGER and use some type
> of code to figure out which SP just updated the current table?

No. At least not without changing all stored procedure to write their
name somewhere. That can be done in a general way, as the global variable
@@procid holds the object id of the currently executing SQL module.
But there is no way to get the entire call stack. Definitely a missing
a feature in SQL Server.

> I tried to run SQL Profiler and i don't understand why i can't
> simply have the Profiler filter events only for the specific database id
> and the table's object id i chose?

The problem with Profiler is that if there are entries that do not
populate the columns you filter on, the NULL values pass the filter
and give you a lot of noise.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links