Thanks for this.
The reason I know is that this is an old app which the vendor isn't supporting anymore.
Sometimes a data issue occurs and we've been told that a hard-coded SP inside the app is the issue.
Yes, SQL profiler is the clear choice indeed but I figured I'd ask if it happened to exist in tempdb, syscomments or other places.
chances are the application is actually just executing TSQL code; going to the trouble of creating a TSQL procedure, executing that TSQL procedure , and then dropping that same TSQL procedure to hide it from view is not something you'd typically see.
the default trace would show it occuring, since it's a DDL action, and any longer term trace you care to create would have it too.
i think it's much more likely "procedure" is referencing a function/procedure/subroutine written in the applications programming code, and not what we think of as a "procedure" in the TSQL world.
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!