There comes a time when mistakes are made. Sometimes those mistakes can be as annoying as a spelling mistake during the creation of a stored procedure. When a mistake such as that happens, we are given a few choices. One could either rename the stored procedure, drop and recreate the stored procedure or simply leave the mistake alone.
When choosing to rename the stored procedure, one may quickly reach for the stored procedure that can be readily used for renaming various objects. That procedure was provided by Microsoft after-all and is named sp_rename. Reaching for that tool however might be a mistake. Here is what is documented about the use of sp_rename to rename a stored procedure. That documentation can be read at this link on MSDN.
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 later in the same documentation, one can read the following.
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.
Now, a chief complaint against dropping and recreating the stored procedure, as recommended, is that process can cause permissions issues. I am less concerned about the permissions issues and see that as more of a nuisance that is easily overcome due to great documentation and a few quick script executions to restore the permissions. Despite that, I think we might have a means to address the rename and permissions issue that will be shared later in this article.
Using sp_rename
When using sp_rename, it would be good to understand what happens and what one might expect to see. Let’s use the following script to create a stored procedure to step through an exercise to rename a stored procedure and evaluate the results.
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'Renamed')
DROP PROCEDURE Renamed
GO
/* This proc is created to search for column names
-- throughout a database
*/CREATE PROCEDURE RenameMe @objname VARCHAR(50)
AS
SELECT o.name AS TableName,c.name AS ColumnName,t.name AS DataType
FROM sys.objects o
INNER JOIN sys.columns c
ON o.object_id = c.object_id
INNER JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.name = @objname
AND o.type = 'u'
GO
SELECT object_id,OBJECT_NAME(sm.object_id) AS ObjName
,definition AS ProcDefinition
FROM sys.sql_modules sm
WHERE OBJECT_NAME(sm.object_id) = 'RenameMe';
GO
SELECT so.object_id, so.name
, OBJECT_NAME(so.object_id) AS FuncObjName
, OBJECT_ID('RenameMe') AS FuncObjID
FROM sys.objects so
WHERE so.name = 'RenameMe';
GO
EXECUTE sp_rename 'RenameMe','Renamed'
GO
SELECT object_id,OBJECT_NAME(sm.object_id) AS ObjName
,definition AS ProcDefinition
FROM sys.sql_modules sm
WHERE OBJECT_NAME(sm.object_id) = 'Renamed';
GO
SELECT so.object_id, so.name
, OBJECT_NAME(so.object_id) AS FuncObjName
, OBJECT_ID('Renamed') AS FuncObjID
FROM sys.objects so
WHERE so.name = 'Renamed';
GOWhen I execute that series of batches, I will get an output that matches the following.
When looking at the results we can see that the use of sp_rename does indeed change the name of the stored procedure as it is represented via sys.objects and metadata. We can also see that the definition of the stored procedure does not change as it is held within the metadata.
If I choose to check the definition through the use of OBJECT_DEFINITION() instead of sys.sql_modules, you will be pleased to know that sys.sql_modules calls OBJECT_DEFINITION() to produce the definition that is seen in the catalog view.
Well, that does pose a potential problem. We see that the object definition is unchanged and may report the name as being different than what the object name truly is. What happens if I execute the stored procedure? Better yet, if I can execute the stored procedure and then capture the sql text associated to that plan, what would I see?
/* Now let's get an execution plan for the proc -- and see what the query text is related to that plan */EXECUTE Renamed 'Last Update Date' GO
Yes! The renamed stored procedure does indeed execute properly. I even get three results back for that execution. Better yet, I get an execution plan which I can pull a plan_hash from in order to evaluate the sql text associated to the plan. In case you are wondering, the execution plan does contain the statement text of the procedure. But for this case, I want to look at the entire definition associated to the plan rather than the text stored in the plan. In this particular scenario, I only see the body of the procedure and not the create statement that is held in metadata.
For this particular execution and plan, I can see a plan_hash of 0xE701AFB2D865FA71. I can now take this and provide it to the following query to find the full proc definition from metadata.
/* Look at the execution plan and get a query_plan_hash from the plan xml */SELECT qs.query_plan_hash,t.text,t.objectid FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t WHERE qs.query_plan_hash = 0xE701AFB2D865FA71 --where 0xE701AFB2D865FA71 was the hash for the proc we --renamed. GO
And after executing that query, I can see results similar to the following.
Now is that because in some way the query that was just run was also running OBJECT_DEFINITION()? Let’s look at the execution plan for both OBJECT_DEFINITION() and the query that was just run.
/* Only produces an estimated plan - not an actual plan */SELECT OBJECT_DEFINITION(OBJECT_ID('Renamed'))
GO
Looking at the XML for that particular plan and we see xml supporting that plan. There is no further function callout and the plan is extremely simple.
Now looking at the plan for the query involving the query_plan_hash we will see the following.
Looking at this graphical plan, we can see that we are calling FNGETSQL. Looking at the XML for this plan, we can verify that FNGETSQL is the only function call to retrieve the full sql text associated to this plan execution. FNGETSQL is an internal function for SQL server used to build internal tables that might be used by various DMOs. You can read just a bit more about that here.
What now?
After all of that, it really looks pessimistic for sp_rename. The procedure renames but does not properly handle metadata and stored procedure definitions. So does that mean we are stuck with drop and create as the Microsoft documentation suggests?
If you have access to the full procedure definition you could issue an alter statement. In the little example that I have been using, I could issue the following statement.
ALTER PROCEDURE Renamed @objname VARCHAR(50) AS SELECT o.name AS TableName,c.name AS ColumnName,t.name AS DataType FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id INNER JOIN sys.types t ON c.user_type_id = t.user_type_id WHERE c.name = @objname AND o.type = 'u' GO
After executing that script, I could check sys.sql_modules once again and find a more desirable result.
SELECT object_id,OBJECT_NAME(sm.object_id) AS ObjName ,definition AS ProcDefinition FROM sys.sql_modules sm WHERE OBJECT_NAME(sm.object_id) = 'Renamed'; GO
And my results…
If you don’t have the text to create the proc, you could use SSMS to script it out for you. It is as simple as right-clicking the proc in question, selecting modify and then executing the script. It should script at with the correct proc name (the beauty of SMO) and then you can get the metadata all up to snuff in your database.
Of course, if you prefer, you could just drop and recreate the procedure. Then reapply all of the pertinent permissions. That is pretty straight forward too.
