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