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