View SQL prepared with sp_prepare

I am troubleshooting an application that use开发者_StackOverflow社区s a SQL Server database and I am seeing a lot of sp_execute calls.

I can not seem to find the sp_prepare calls.

How can you inspect all of the prepared SQL statements in memory?

---

I was looking for a way to see the actual SQL statements executed by sp_execute in SQL Server 2008 R2 Profiler.

To do this, I created a new trace, and clicked on the "Events Selection" tab. I selected "Show all events" and checked Stored Procedures > SP:StmtCompleted. Running the trace, I was then able to see the actual SQL statements.

---

I ran into this issue as well. SQL Profiler was not capturing the sp_prepare statement because it occurred before the SQL Profiler trace had started to run. The various postings that rely on sys.dm_exec_sql_text did not help because I could not find the correct sql_handle or plan_handle value to provide for that stored procedure.

I found a solution from this blog post: in SQL Profiler, click the "Show all events" checkbox and then under the "Stored Procedures" heading choose "SP:CacheHit".

In the resulting SQL Profiler output, you'll see an "SP:CacheHit" row containing the cached SQL statement near your "RPC:Starting ... sp_execute" statement.

You can then reconstruct and reexecute the full SQL statement in SSMS if you wish using:

exec sp_executesql @stmt=N'{statement from SP:CacheHit}', 
  @params=N'{parameter declaration from  SP:CacheHit}',
  @param1={value}, {...parameters from RPC:Starting sp_execute statement}

---

Following up on my comment above, I found a number of relevant links:

How can I find out what command sp_execute is running (without using Profiler)

SP_EXECUTE executing... what?

See the query in sp_execute

Microsoft has documentation but it maybe a challenging piecing things together (as always). If the plan handle is known, you can use this:

sys.dm_exec_sql_text (Transact-SQL)

This is a table-valued function. You can see a blog article here that exploits such table-valued functions to retrieve object dependencies for a valid handle of a compiled (prepared) plan.