Renamed Stored Procedures Danger

Renamed stored procedure can be head ache for some cases.

For example, lets say that you renamed a stored procedure SP_ABC to SP_ABX and created a new stored procedure with old name SP_ABC. If you select definition for those 2 stored procedure from sys.sql_modules view, you will see that both definitions are the same as create procedur SP_ABC. 

If you want to change  SP_ABX stored procedure and get definition from sys.sql_modules as changing it to as ALTER, you will change SP_ABC procedure instead of SP_ABX. 

We must be careful about this case. The following script finds renamed stored procedures..

select OBJECT_SCHEMA_NAME(object_id)+ '.'+object_name(object_id) as [Object_Name],  substring(definition,1,100),  * from sys.sql_modules
where PATINDEX('%'+object_name(object_id)+'%',definition)  = 0
order by [Object_Name]