We help IT Professionals succeed at work.

Stored Procedure Name Not Matching Definition After F2 Rename

friedcranium
friedcranium asked
on
I am having trouble generating stored procedures into a script file for backup purposes.  I am using the attached query to get the stored procedure information.  I then use BCP to export the dataset to a text file.  The issue I have come across it that if any stored procedure name is changed using F2 or rename in SSMS, the name change is done in the name field of sys.objects but is not done in the definition field of sys.sql_modules.  So when my procedure is run to create the backup file, the stored procedure will have the incorrect name in the CREATE PROCEDURE statement.  Does anyone know of a better way to export stored procedures to a script file that will have the correct procedure names?


WITH ROUTINES AS 
   (
      -- CANNOT use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit
      SELECT o.type_desc AS ROUTINE_TYPE
            ,o.[name] AS ROUTINE_NAME
            ,m.definition AS ROUTINE_DEFINITION
         FROM sys.sql_modules AS m
           INNER JOIN sys.objects AS o
              ON m.object_id = o.object_id
         WHERE (o.type = 'P' OR o.type = 'FN') 
            AND o.is_ms_shipped = 0
   )
SELECT ROUTINE_DEFINITION + 'GO' AS ROUTINE_DEFINITION FROM ROUTINES

Open in new window

Comment
Watch Question

View Solution Only

Senior .Net Consultant
Top Expert 2016
Commented:
all the software that read the definition (for example Red Gate SQL Search) have this problem. the only way I found is to recompile the SP
friedcranium

Author

Commented:
Thanks.  Looking in Reg Gates forums lead me to the sp_rename documentation which does state that only drop and create will update the script.  I adjusted my query to try and adjust if the name isn't in the script as a precaution later by getting the procedure name after the CREATE PROCEDURE and replacing it with the name in the name field.  Granted it isn't perfect but works for my needs now.
WITH ROUTINES AS 
   (
      -- CANNOT use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit
      SELECT o.type_desc AS ROUTINE_TYPE
         ,o.[name] AS ROUTINE_NAME
         , CASE WHEN CHARINDEX('[' + o.[name] + ']', m.definition) > 0
                THEN m.definition 
                ELSE REPLACE(m.definition,
                              SUBSTRING(m.definition, 
                                 CHARINDEX('CREATE PROCEDURE ', m.definition) + LEN('CREATE PROCEDURE ') + 1, 
                                 (CHARINDEX(' ', m.definition, CHARINDEX('CREATE PROCEDURE ', m.definition) + LEN('CREATE PROCEDURE ') + 1))
                                    - (CHARINDEX('CREATE PROCEDURE ', m.definition) + LEN('CREATE PROCEDURE ') + 1)),
                                 '[dbo].[' + o.[name] + ']')
                END AS ROUTINE_DEFINITION
      FROM sys.sql_modules AS m
         INNER JOIN sys.objects AS o
            ON m.object_id = o.object_id
      WHERE (o.type = 'P' OR o.type = 'FN') 
         AND o.is_ms_shipped = 0
   )
SELECT ROUTINE_DEFINITION + CHAR(13) + 'GO' AS ROUTINE_DEFINITION 
   FROM ROUTINES

Open in new window