CODEWITHSUNDEEP
sqlsql-server
Mandragorasprout
Mandragorasprout

Reputation: 123

Find and replace content in stored procedures ms sql server

I want to rename tables and views which are used in stored procedures. Is there any way to find and replace table names in stored procedures, maybe there is tool for ms sql server (i'm using ms sql server 2012).

Upvotes: 0

Views: 6062

Answers (3)

Bruno Scatena
Bruno Scatena

Reputation: 21

I answered this on another topic (https://stackoverflow.com/a/67728039/11165834) , I do it using the following script:

DECLARE @queryDef NVARCHAR(max)

WHILE EXISTS (
                    SELECT 1
                    FROM sys.sql_modules    sm 
                    JOIN    sys.objects  o ON sm.object_id = o.object_id 
                    WHERE   sm.definition LIKE '%TEXT_TO_REPLACE%' 
                    AND o.type = 'V'
            )
BEGIN
    
                                                                    -- TO ALTER THE VIEW AUTOMATICALLY                                          
    SET @queryDef = (   SELECT TOP 1 Replace (Replace (sm.definition, 'CREATE VIEW', 'ALTER VIEW'), 
                                            'TEXT_TO_REPLACE', 
                                            'NEW_TEXT') 

                        FROM sys.sql_modules    sm 
                        JOIN    sys.objects  o ON sm.object_id = o.object_id

                        WHERE   sm.definition LIKE '%TEXT_TO_REPLACE%'
                        AND     o.type = 'V')
    
    EXEC (@queryDef)

END

I use it to replace procedures/views when I restore a backup from production into tests databases.

As @S.A said, be verry careful because is not a verry safe way. Change the "o.type" and "Replace (sm.definition, 'CREATE VIEW', 'ALTER VIEW'" accordingly to your need

Upvotes: 0

S.A
S.A

Reputation: 196

SQL Server might not allow you to directly UPDATE the object definitions (Views and Stored Proceduress in your case) present in the System catalogs even after setting the 'Allow Updates' option to 1.

The following code will generate the required ALTER Script and you can run them manually after reviewing the definitions ([ModifiedDefinition] )or u can loop through each value of [ModifiedDefinition] and run it using sp_executesql.

SELECT 
    b.Name                                                                      AS [ObjectName],
    CASE WHEN b.type ='p' THEN 'Stored Procedure'
         WHEN b.type ='v' THEN 'View' 
         ELSE b.TYPE 
    END                                                                         AS [ObjectType]
    ,a.definition                                                               AS [Definition]
    ,Replace ((REPLACE(definition,'OLD Value','New Value')),'Create','ALTER')   AS [ModifiedDefinition]
FROM sys.sql_modules a
JOIN 
(   select type, name,object_id
    from sys.objects
    where type in (
            'p' -- procedures
            ,'v'--views
                   )
  and is_ms_shipped = 0 
 )b
 ON a.object_id=b.object_id

And as always, be careful with production data and take backups before performing bulk changes on object definitions!!

Upvotes: 3

Archer
Archer

Reputation: 1162

You can use DBvisualizer .. it pretty much works with all databases and with ms sql too, you can do all you mentioned by using this.

Upvotes: 2

Related Questions