question

Saur avatar image
Saur asked

Stored procedure definition after sp_rename

Hi, I'm looking for a way to automatically (read dynamically) refresh stored procedures definition so that the definition found in sys.sql_modules matches the actual stored procedure definition. Indeed, after a procedure is renamed using sp_rename, the definition isn't updated to reflect the correct create statement of the stored procedure. As of now, I'm trying to rebuild the definition using the following script: SELECT r.name, SUBSTRING(CreateStatement.Code, 0, CreateStatement.[CreateIndex]) + 'CREATE PROC [dbo].[' + r.name + ']' + CHAR(10) + SUBSTRING(CreateStatement.Code, CreateStatement.[CreateIndex] + CodeStatement.[Index] -1, LEN(Def.cleanedDefinition)) AS [Definition] FROM sys.procedures r INNER JOIN sys.sql_modules m ON r.object_id = m.object_id CROSS APPLY(SELECT LTRIM(REPLACE(m.definition, '-- Stored Procedure','')) AS cleanedDefinition) AS Def CROSS APPLY(SELECT PATINDEX('%CREATE[^a-zA-Z]PROC%', LTRIM(Def.cleanedDefinition)) AS [CreateIndex], LTRIM(Def.cleanedDefinition) AS Code, SUBSTRING(Def.cleanedDefinition,PATINDEX('%CREATE[^a-zA-Z]PROC%', Def.cleanedDefinition),LEN(Def.cleanedDefinition)) AS AfterCreateCode FROM sys.sql_modules rr WHERE m.object_id = rr.object_id ) AS CreateStatement CROSS APPLY(SELECT MIN([Index]) AS [Index] FROM ( SELECT PATINDEX('%@%', CreateStatement.AfterCreateCode) AS [Index] UNION ALL SELECT PATINDEX('%(%', CreateStatement.AfterCreateCode) AS [Index] UNION ALL SELECT PATINDEX('%AS%', CreateStatement.AfterCreateCode) UNION ALL SELECT PATINDEX('%' + CHAR(10) + 'AS%', CreateStatement.AfterCreateCode) ) A WHERE A.[Index] >0 ) AS CodeStatement WHERE r.type = 'P' This script is meant to handle most cases (comments before create statement, CREATE PROC vs CREATE PROCEDURE, braquets, etc...) but it doesn't seem to cover them all, that's why I'm starting to think it could actually be smarter to refresh SP definitions before hand. The purpose of this request is to be able to list and script every stored procedures on one server in order to duplicate them on another one (kind of what SQL compare does), using SQL scripts only. Thanks for you help! Saur
stored-proceduresdynamic-sqlrenamedefinition
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Requesting to please move this question from the "meta-askssc" section to the "default" section
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
Do you have some specific edge cases where your script isn't working? It looks like a commented out CREATE PROCEDURE statement before the "real" one might catch you out. "I'm starting to think it could actually be smarter to refresh SP definitions before hand" <- that is probably the way to go :) "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 "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." Both from http://msdn.microsoft.com/en-us/library/ms188351.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.