Listing prepared statements...

Hi...
I've noticed when using Microsoft SQL Profiler, when prepared statements are
in use, yields only such information as "sp_execute 3, 12, 34" etc. This
contrasts with using stored procedures, whereby I get the entire "Select"
statement in the profiler output.
I know if I enable events in the profiler, such as TSQL>Exec Prepared SQL
and TSQL>Prepare SQL, I get to see the initial preparation of the statement,
and I'm able to resolve statement handle does what. (For example, in my
example above, what does statement '3' actually do?)
But what about if I start the profiler AFTER the statements have been
prepared (and assigned to their various numeric handle numbers)?
Is there a query or method by which I can display all currently-prepared SQL
statements?
Thanks for any help,
Mark.
Mark wrote:
> Hi...
> I've noticed when using Microsoft SQL Profiler, when prepared
> statements are in use, yields only such information as "sp_execute 3,
> 12, 34" etc. This contrasts with using stored procedures, whereby I
> get the entire "Select" statement in the profiler output.
> I know if I enable events in the profiler, such as TSQL>Exec Prepared
> SQL and TSQL>Prepare SQL, I get to see the initial preparation of the
> statement, and I'm able to resolve statement handle does what. (For
> example, in my example above, what does statement '3' actually do?)
> But what about if I start the profiler AFTER the statements have been
> prepared (and assigned to their various numeric handle numbers)?
> Is there a query or method by which I can display all
> currently-prepared SQL statements?
> Thanks for any help,
> Mark.
You might be able to have a look in master..syscacheobjects. Try
filtering on the objtype column for "Prepared statement".
David Gugick
Quest Software
www.quest.com
|||Thanks David,
I'll give it a try.
Mark.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OEuWHctIGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Mark wrote:
> You might be able to have a look in master..syscacheobjects. Try
> filtering on the objtype column for "Prepared statement".
>
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||David,
Again, thank you very much, fantastic! Yes, you're right, there in
syscacheobjects is the SQL statement and dbid related to prepared
statements.
Mark.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OEuWHctIGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Mark wrote:
> You might be able to have a look in master..syscacheobjects. Try
> filtering on the objtype column for "Prepared statement".
>
> --
> David Gugick
> Quest Software
> www.quest.com
>