We help IT Professionals succeed at work.
Get Started

Stored Procedure Name Not Matching Definition After F2 Rename

501 Views
Last Modified: 2012-08-14
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
Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE