CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Jason
Jason

Reputation: 1325

Find syntactically matching stored procs in SQL Server

I was Googling around, but couldn't find anything that can help me. We are using SQL-Server 2008 R2, and it's our policy to use stored procedures for all our database calls. This works fine, but the problem is now that our list of procedures has grown to over 600, we're making duplicates of syntactically matching queries that someone else (and sometimes ourselves) wrote.

Right now we search for all the procs that contain the same tables and columns as the one we want to create and then see if we can just reuse it, but this is becoming a more and more time consuming activity. If we try anything more complicated than just searching table/view and column names, then we might miss an existing proc simply due to the syntax it happened to use.

Are there any tools which can take a query and tell you which procs are, or at the very least suggest could be, syntactically equal to your query? Or, barring that, what methods do you use to ensure you don't have multiple procs containing the same query written slightly differently?

Upvotes: 1

Views: 91

Answers (3)

Martin Smith
Martin Smith

Reputation: 453243

This might find some dupes as long as the plans for the stored procs are in cache. (The query_hash and query_plan_hash are explained here)

WITH qs AS
(
SELECT *, 
       COUNT(*) OVER (PARTITION BY query_hash) qh,
       COUNT(*) OVER (PARTITION BY query_plan_hash) qph
FROM sys.dm_exec_query_stats 
)
SELECT 
       db_name(dbid) as database_name,
       object_name(objectid,dbid) as object_name,
       text,
         SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1, (
              ( CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END -
       qs.statement_start_offset ) / 2 ) + 1) AS statement_text,
                query_hash,
       query_plan_hash 
FROM qs
CROSS APPLY  sys.dm_exec_sql_text (qs.sql_handle) st
WHERE qh>1 OR qph>1
ORDER BY qh, qph

Upvotes: 2

Neville Kuyt
Neville Kuyt

Reputation: 29629

This is one of the reasons I'm not a huge fan of stored procedures - managability becomes a real issue.

As Aaron Bertrand says, naming conventions go a long way with this, but if you haven't followed any particular convention, that won't solve your problem.

I don't think there's an out-of-the-box solution for this - but I think you could at least simplify your search by using sp_depends.

So, if you are looking for a proc that affects tables a, b and c, running sp_depends with a, b and c and seeing which procs turn up for all 3 would at least tell you which of the 600 procs you need to read through.

sp_depends doesn't report procs using dynamic sql.

Upvotes: 1

anon
anon

Reputation:

Using (and enforcing!) consistent naming standards should go a long way in preventing duplicates. Always use the same style (my preference is entity_action, e.g. Customer_Update). If all your objects are consistently named, it becomes pretty hard to create a duplicate unless you're completely ignoring the standard.

I realize that doesn't help you now - I don't know of any way to determine this unless you either grep by brute force or try to match query plans in sys.dm_exec_cached_plans to see if similar/identical plans refer to different objects. Neither of these things are trivial.

Upvotes: 2

Related Questions