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