Find redundant stored procedures which have the same body

Question

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.

Answer 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 stored procedure body.

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

Link to the free download from Redgate's website / some more info about the SQL Search plugin

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