Running dynamic SQL on a database

You can call <dbname>.sys.sp_executeSQL to run dynamic SQL on a database, but what if you want to run dynamic SQL on a list of databases you have as strings?

I have used this to perform maintenance tasks on sets of related databases that could have different names (e.g. if they run multiple instances of our product’s databases on the same SQL server instance), with great success.

ALTER PROCEDURE [Admin].[Infrastructure_ExecuteSQL]
(
@sql NVARCHAR(MAX),
@dbname NVARCHAR(MAX) = NULL
)
AS BEGIN
/*
PURPOSE
Runs SQL statements in this database or another database.
You can use parameters.

TEST
EXEC dbo.Infrastructure_ExecuteSQL 'SELECT @@version, db_name();', 'MCSAuditLog';

REVISION HISTORY
20180803 DKD
Created
*/

/* For testing.
DECLARE @sql NVARCHAR(MAX) = 'SELECT @@version, db_name();';
DECLARE @dbname NVARCHAR(MAX) = 'mcslottery';
--*/

DECLARE @proc NVARCHAR(MAX) = 'sys.sp_executeSQL';
IF (@dbname IS NOT NULL) SET @proc = @dbname + '.' + @proc;

EXEC @proc @sql;

END;

Advertisement