How do I run SQL queries on different databases dynamically?

Question

I have a sql server stored procedure that I use to backup data from our database before doing an upgrade, and I'd really like it to be able to run the stored procedure on multiple databases by passing in the database name as a parameter. Is there an easy way to do this? The best I can figure is to dynamically build the sql in the stored procedure, but that feels like its the wrong way to do it.

Answer 1

personally, i just use a batch file and shell to sqlcmd for things like this. otherwise, building the sql in a stored proc (like you said) would work just fine. not sure why it would be "wrong" to do that.

best regards, don

Answer 2

There isn't any other way to do this. Dynamic SQL is the only way; if you've got strict controls over DB names and who's running it, then you're okay just truncating everything together, but if there's any doubt use QUOTENAME to escape the parameter safely:

CREATE PROCEDURE doStuff
    @dbName NVARCHAR(50)
AS
    DECLARE @sql NVARCHAR(1000)

    SET @sql = 'SELECT stuff FROM ' + QUOTENAME(@dbName) + '..TableName WHERE stuff = otherstuff'

    EXEC sp_ExecuteSQL (@sql)

Obviously, if there's anything more being passed through then you'll want to double-check any other input, and potentially use parameterised dynamic SQL, for example:

CREATE PROCEDURE doStuff
    @dbName NVARCHAR(50)
    @someValue NVARCHAR(10)
AS
    DECLARE @sql NVARCHAR(1000)

    SET @sql = 'SELECT stuff FROM ' + QUOTENAME(@dbName) + '..TableName WHERE stuff = @pOtherStuff'

    EXEC sp_ExecuteSQL (@sql, '@pOtherStuff NVARCHAR(10)', @someValue)

This then makes sure that parameters for the dynamic SQL are passed through safely and the chances for injection attacks are reduced. It also improves the chances that the execution plan associated with the query will get reused.

Answer 3

MSSQL has an OPENQUERY(dbname,statement) function where if the the server is linked, you specify it as the first parameter and it fires the statement against that server.

you could generate this openquery statement in a dynamic proc. and either it could fire the backup proc on each server, or you could execute the statement directly.

Do you use SSIS? If so you could try creating a couple ssis packages and try scheduling them,or executing them remotely.

Answer 4

build a procedure to back up the current database, whatever it is. Install this procedure on all databases that you want to backup.

Write another procedure that will launch the backups. This will depend on things that you have not mentioned, like if you have a table containing the names of each database to backup or something like that. Basically all you need to do is loop over the database names and build a string like:

SET @ProcessQueryString=
    'EXEC '+DatabaseServer+'.'+DatabaseName+'.dbo.'+'BackupProcedureName   param1, param2'

and then just:

EXEC (@ProcessQueryString)

to run it remotely.