how to run once a bunch of generated scripts with exception handling?

Question

Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists.

So I created a script that calls a procedure that will save the contents of this table in a DBA database, for all databases where this table exists.

That is working fine, but I would like to automate this process, avoiding the copy and paste - see below.

How it works: When I run the following script I get a bunch of scripts to be executed:

--============================================================================
-- to save the table dbo.ref_systemSettings in all the databases where it exists
-- just run the generated script below at Radhe
--============================================================================
SELECT name, Radhe='exec sp_save ' + '''' 
     + name + '.dbo.ref_systemSettings' + ''''
FROM sys.databases
WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

the query above produce the below:

which I copy and paste, in order to run:

exec sp_save 'my_db_01.dbo.ref_systemSettings'
exec sp_save 'my_db_02.dbo.ref_systemSettings'
exec sp_save 'my_db_03.dbo.ref_systemSettings'
exec sp_save 'my_db_04.dbo.ref_systemSettings'

when I run the whole of the query above, all my tables are saved into the DBA database. This is currently working fine.

Question:

I would like to eliminate the copy and paste. what could be an effective way to run these scripts, while taking into consideration exception handling, lets say... there is no more space in the DBA database and the table cannot be saved... or any other expection??

Answer 1

The result should be stored in a variable and should execute that SQL. It's called dynamic SQL. You can read more about this here

      BEGIN TRY

       DECLARE @sql varchar(max)
        SELECT @sql = COALESCE(@sql ,'')+'exec sp_save ' + '''' 
             + name + '.dbo.ref_systemSettings' + ''''+';'
        FROM sys.databases
        WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

        EXEC (@sql)

       END TRY
       BEGIN CATCH

           DECLARE @ErrorMessage NVARCHAR(4000);  
           DECLARE @ErrorSeverity INT;  
           DECLARE @ErrorState INT;  

          SELECT   
             @ErrorMessage = ERROR_MESSAGE(),  
             @ErrorSeverity = ERROR_SEVERITY(),  
             @ErrorState = ERROR_STATE();  

          -- Use RAISERROR inside the CATCH block to return error  
          -- information about the original error that caused  
          -- execution to jump to the CATCH block.  
          RAISERROR (@ErrorMessage, -- Message text.  
                     @ErrorSeverity, -- Severity.  
                     @ErrorState -- State.  
                    ); 

       END CATCH

Answer 2

You could use a cursor and execute each row at a time:

DECLARE @qstr varchar(1000)
DECLARE cur_query CURSOR FAST_FORWARD READ_ONLY FOR   
    SELECT Radhe='sp_save ' + '''' 
    + name + '.dbo.ref_systemSettings' + ''''
    FROM sys.databases
    WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

OPEN cur_query  
FETCH NEXT FROM cur_query INTO @qstr  

IF @@FETCH_STATUS <> 0   
    PRINT 'Nothing'

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @qstr
    exec (@qstr)
    FETCH NEXT FROM cur_query INTO @qstr
END  

CLOSE cur_query  
DEALLOCATE cur_query