This post is one of the request from OP.
Download SQL Backup SP in the same database
The probelm statement 1:-
Create a backup copy of a group of stored procedures on the same database. Using TSQL I need to find all stored procedures that meet a criteria (WHERE pr.name = ‘%_SomeSuffix’) and create that same procedure in the same database with a different name (‘%_SomeSuffix_BAK).
DECLARE @sql nvarchar(max) DECLARE @Name varchar(100)='AdventureWorks2008' DECLARE c CURSOR FOR SELECT replace(mod.definition,pr.name,pr.name+'_BAK') FROM sys.procedures pr INNER JOIN sys.sql_modules mod ON pr.object_id = mod.object_id WHERE pr.Is_MS_Shipped = 0 AND pr.name LIKE '%PROC_RowCount' OPEN c FETCH NEXT FROM c INTO @sql WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = REPLACE(@sql,'''','''''') SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')' EXEC(@sql) FETCH NEXT FROM c INTO @sql END CLOSE c DEALLOCATE c
The probelm statement 2:-
Create a backup copy of a group of stored procedures on the same database. Using TSQL I need to find all stored procedures that meet a criteria (WHERE definition = ‘%_SomeSuffix’) and create that same procedure in the same database with a different name (ProcName_BAK).
uspGetBillOfMaterials has the following search pattern in the definition
WITH[BOM_cte]
DECLARE @sql nvarchar(max) DECLARE @Name varchar(100)='AdventureWorks2008' DECLARE c CURSOR FOR SELECT replace(mod.definition,pr.name,pr.name+'_BAK') FROM sys.procedures pr INNER JOIN sys.sql_modules mod ON pr.object_id = mod.object_id WHERE pr.Is_MS_Shipped = 0 AND mod.definition LIKE '%WITH [BOM_cte]%' OPEN c FETCH NEXT FROM c INTO @sql WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = REPLACE(@sql,'''','''''') SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')' EXEC(@sql) FETCH NEXT FROM c INTO @sql END CLOSE c DEALLOCATE c
