Changing database inside a query dynamically

There may be a time where you need to execute a change on one database, and then another. An example could be a change against a transaction database, and the making a similar change to an associated history/audit database.

It possible to do, but you’re going to have to use dynamic SQL to get it done.

Here’s an example

ALTER TABLE [User] ADD [MiddleName] NVARCHAR(100)
GO

DECLARE @sql NVARCHAR(MAX), @HistoryDatabase NVARCHAR(50)
SELECT @HistoryDatabase = VARCHAR FROM Constants WHERE Name = 'HistoryDatabaseName'

SET @sql = 
'USE [' + @HistoryDatabase + ']
ALTER TABLE [User] ADD [MiddleName] NVARCHAR(100)
'
EXEC (@sql)
GO

Do note, you’re not going to see the connected database change if you run the script in SQL Server Management Studio. The database change only happens within the context of the executed script. Once complete, the connection transfers back to the original database.

I hope that helps!

Subscribe

0 comments