question

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