SQL Server: discovering non-parameterized queries

Question

I have a client that has had programming done by past developers. Their code has recently become suspect, and I would like to know if they use parameterized queries. I was hoping I discovered non-parameterized queries through SQL Server, but I did not find a way to do this. I understand that not all queries need to be parameterized, since the query may be something like

select count(*) from Customers

But if SQL Server can tell me programmatically which queries have some string constants instead of entering parameters, that would be great. By the way, registering all SQL queries and deleting all rows with the @ symbol is close, but below the query will be considered legal:

select * from Users where Username='user' and Password=@Password

So I really need SQL Server to read the contents of this command and determine if all inputs are parameterized. Thank.

Answer 1

You are actually looking for adhoc requests (not stored procedures, not sp_executesql).

You can start by watching the sys.dm_exec_cached_plansDMV:

SELECT
    CP.usecounts
    , CP.cacheobjtype
    , ST.text as sql_Statement    
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    ST.dbid = DB_ID()
    AND CP.objtype IN ( 'Adhoc')
ORDER BY
    CP.usecounts DESC

Just remember that Relational Engine could parameterize simple queries (a simple parameterization function ), so you can have the same lines as

, adhoc-, optimize for ad hoc workloads, Relational Engine, = > (-).

. , , .

Answer 2

Mihai , dm_exec_cached_plans. ( , , [syscacheobjects] ). .

, , , WHERE. dm_exec_cached_plans, , WHERE, :

SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE [text] LIKE '%WHERE%''%'
----AND [text] NOT LIKE '%sp_executesql%'  ----queries probably ok, with sp_executesql
----WHERE usecounts > 1   ----un-commenting this might also be interesting to check out. 
ORDER BY usecounts DESC;

Mihai:

SELECT
    CP.usecounts
    , CP.cacheobjtype
    , ST.text as sql_Statement    
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    CP.objtype = 'Adhoc'
    AND ST.dbid = DB_ID()
    AND ST.text LIKE '%WHERE%''%'
ORDER BY
    CP.usecounts DESC

ORDER BY, , . , , , ... , (, ..) , ( sp_executesql), , usecounts = 1.

, ( " SQL- ?" ): , " ", ... SQL. (http://www.sqlservercentral.com/Forums/Topic1375781-391-1.aspx http://technet.microsoft.com/en-us/library/ms181055%28v=sql.105%29.aspx)

, ...