0

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: enter image description here

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??

2 Answers 2

3

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
1
  • 1
    @marcellomiorelli, I used COALESCE method for string concatenation, hope it's not a big result set, if it is or you need an ordered execution; let's say you want to execute the sp in a particular order use XML method for string concatenation with an order by cluase defined
    – Biju jose
    Aug 20, 2018 at 17:12
1

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  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.