Old name of the procedure in sp_helptext

Question

Some time ago the procedure ALF_GetPopulationForPolygon_2_old was created. Then in SSMS, it was renamed (right mouse button -> rename) to the procedure ALF_GetPopulationForPolygon_2_old_FOR_DELETE_20170504. Renaming passed normally, but as a result, the query:

SELECT
    sm.definition
FROM
    sys.objects AS o
        INNER JOIN sys.sql_modules AS sm
        ON ( 1 = 1 )
           AND ( sm.object_id = o.object_id )
WHERE
    ( 1 = 1 )
    AND ( o.name like 'ALF_GetPopulationForPolygon_2_old_FOR_DELETE_20170504' )

And calling the stored procedure:

EXEC sp_helpText 'ALF_GetPopulationForPolygon_2_old_FOR_DELETE_20170504'

Return the following text:

...
CREATE PROCEDURE [dbo]. [ALF_GetPopulationForPolygon_2]
...

Those show the OLD(!) name of the procedure. Do you have any idea what this is caused, and how can this be fixed?

Answer 1

You shouldn't rename your objects for at least two reasons.

If you'd use a T_SQL command instead of GUI (all that you "clicks" in Management Studio produces T-SQL coede behind the scenes), you'd get a warning, the same you can see in BOL article: sp_rename (Transact-SQL)

CAUTION Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

And it was the first reason against renaming. Scrolling the article find the Remarks section:

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

And this was the second.

So when you want to rename your procedure you should script your procedure as CREATE and change the procedure name, doing so you recreate your proc with the new name and then you can drop the old one