0

How to delete all redundant stored procedures in T-SQL? By saying "redundant", I am talking about SPs that may have different names, but work the same way -- same input, same output. The approaches can be different.

Here is the situation: 20-30 databases with hundreds of tables each, thousands of stored procedures which keeps growing everyday, no version control on stored procedures, and everything we did is on the production databases, no test databases.

wxw
  • 45
  • 5
  • it's never too late to start VCing your stuff. As far as identifying redundant stored procs... I don't think there's any way you're going to do this aside from the hard way. – Kritner Jun 29 '15 at 18:12

1 Answers1

0

Profiler and hard work are the answer. Set profiler to monitor the RPC:Completed event. Remove all possible columns and add the "Text Data" column. The output could be saved then parsed to find stored procedures which have the same paramertes and produce the same output. Check Stored procedure output parameters in SQL Server Profiler for more information on the results of the RPC:Completed event.

Community
  • 1
  • 1
RC_Cleland
  • 2,274
  • 14
  • 16