List Prepared Statements

Question

Anybody know how I can list the prepared statements that are in a SQL Server instance?

Answer 1

select
    cp.objtype,
    st.text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.objtype = 'prepared'

That above query will return all of the prepared plans that are cached, as well as their corresponding SQL text.