Thursday, February 11, 2016

SQL - Find Stored Procedures that have been Renamed

The following script will determine if any stored procedures were renamed, rather than dropped and re-created (substitute [DATABASE NAME] with the name of your database):


     SELECT meta.ROUTINE_NAME, *
     FROM [DATABASE NAME].sys.sql_modules def,
           [DATABASE NAME].INFORMATION_SCHEMA.ROUTINES meta
     WHERE  SUBSTRING(def.definition,1,4000) = SUBSTRING(meta.ROUTINE_DEFINITION,1,4000)
AND def.definition NOT LIKE '%![' + meta.ROUTINE_NAME + '!]%' ESCAPE '!'   
order by 1