T-SQL – Scripting Stored Procedure to Create backup copy on same database

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(maxDECLARE @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(maxDECLARE @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 
 Output
Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, T-SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s