TSQL - Remove Database Name Or Some Text From Stored Procedures [ Cursor Example ]

Scenario: 

Sometime when developers create stored procedure they use DatabaseName.SchemaName.StoredProcedureName. It works fine as long as the database name stays same but sometime we have to restore this database with different name. Then the problem starts. The stored procedures try to point to the database but database name has changed. To make this correction we have to alter all stored procedures and remove database name from the definition.

Solution :

Here is code that can loop through all the Stored procedure definitions and remove the text which we want and recreate them.

DECLARE @TextToRemove VARCHAR(100)

SET @TextToRemove='Test1.' -- Provide the text that you want to remove

DECLARE @SpName VARCHAR(100)

DECLARE @SchemaName VARCHAR(100)

DECLARE @SpDefinition NVARCHAR(MAX)
--Declare Cursor

DECLARE CUR CURSOR FOR
  SELECT Name,
         definition,
         Schema_name(schema_id) AS SchemaName
  FROM   sys.procedures P
         INNER JOIN sys.sql_modules M
                 ON M.OBJECT_ID = P.OBJECT_ID
  WHERE  is_ms_shipped = 0
  AND definition LIKE  '%' + @TextToRemove + '%'
--OPEN CURSOR

OPEN CUR
--Fetch First Row

FETCH NEXT FROM CUR INTO @SPName,@SpDefinition,@SchemaName
--Loop

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(MAX)
      DECLARE @DropSPSQL NVARCHAR(MAX)
      SET @DropSPSQL=NULL

      SET @DropSPSQL='Drop Procedure [' + @SchemaName + '].['
                     + @SpName + ']'
      SET @SQL=NULL
      SET @SQL=REPLACE(@SpDefinition, @TextToRemove, '')
--Drop SP
--Print @DropSPSQL

EXEC (@DropSPSQL)

--Create SP

EXEC(@SQL)

      PRINT @SpName+ ' :: Is recreated after removing '+ @TextToRemove

      FETCH NEXT FROM CUR INTO @SPName,  @SpDefinition,@SchemaName
  END
--Close and Deallocate Cursor

CLOSE CUR
DEALLOCATE CUR