-2

I have a database and there are some stored procedures with different names but the same body. How can I identify them dynamically using a T-SQL statement?

I cannot use definition column of information_schema.routines tables because it has create procedure name in it. Hence without that how can I check dynamically redundant procedures.

I tried using different joins and routines tables but couldn't find the right solution.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Srinivas
  • 11
  • 1
    If you knew the name of the SP, perhaps from another column in `information_schema.routines`, you could `replace` the name in the definition with a placeholder, e.g. `'Beatrice'`, and compare the resulting definitions. Slightly error prone, but a start. – HABO Aug 21 '23 at 18:24
  • Why does it have to be in T-SQL? Use the right tool for the job. I would probably use external tools that are much better at parsing T-SQL than T-SQL - which is really sloppy and bad at it. .NET has a much more powerful and accurate [TSqlParser](https://codetype.wordpress.com/2012/11/01/net-sql-parsing-using-the-tsqlparser-library/) library you can use from PowerShell, C#, etc. – Stuck at 1337 Aug 21 '23 at 20:30
  • 2
    The proc name is part of the proc body stored in the SQL Server catalog views. The robust way to consider only the text after `CREATE PROCEDURE` for comparison is with a T-SQL parser (e.g. [TSql160Parser](https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsql160parser?view=sql-transactsql-161) but since that's not the T-SQL statement you requested, I won't add an example as an answer. – Dan Guzman Aug 21 '23 at 20:33
  • 1
    I [wrote a series on parsing _parameters_ from stored procedure bodies](https://sqlperformance.com/2020/09/sql-performance/paramparser-1) and demonstrated why it's hard even in languages that are better at it than T-SQL. Particularly, comments in the body of one procedure or another can make redundant procedures impossible to identify. – Aaron Bertrand Aug 21 '23 at 20:47
  • @AaronBertrand, one can use the parser to ignore comments and whitespace (assuming not relevant for the task) but it requires processing the underlying token stream to include only the ones needed. – Dan Guzman Aug 21 '23 at 20:57
  • 2
    @Dan Indeed. Wasn't trying to flesh out all the nuance, just that you'll have to write something, and there are better tools than T-SQL. – Aaron Bertrand Aug 21 '23 at 21:07
  • It cannot be a TSQLParser. It only needs to be a T-SQL, Moreover like a dynamic stored procedure. – Srinivas Aug 22 '23 at 09:15
  • 1
    Still doesn't explain _why_ it needs to be T-SQL. "I need a bucket made of velcro." Velcro isn't the right tool to carry water, and T-SQL isn't the right tool to parse T-SQL. – Stuck at 1337 Aug 22 '23 at 12:16

1 Answers1

1

I know your question indicated you are looking to do this inside of SQL, but alternatively you could look at using something like SQL Search to find these duplicates based on key words inside your sproc body.

On object types of procedures and a matching based on text.

image showing list of object types available in sql search

image showing list of matching types inside of sql search

Link to the free download from redgates website / some more info about the sql search plugin

https://www.red-gate.com/products/sql-search/

Marmiton
  • 29
  • 2
  • 9