Dynamically change database Name without using dynamic SQL in SQL Server

Sometimes there is needed to change database name of a table dynamically as passed through parameter. To do this we have to write transact SQL script as a dynamic query.   This sometimes reduced readability. Today we are going to discuss an approach to change database name or context without using dynamic SQL Query.

Let’s assume our application uses four databases:

CREATE DATABASE DB1

CREATE DATABASE DB2

CREATE DATABASE DB3

CREATE DATABASE WorkDB

Database named DB1, DB2 and DB3 has a table named Data:

USE DB1

CREATE TABLE dbo.Data(Value VARCHAR(10))

INSERT dbo.Data VALUES(DB_NAME())

GO

USE DB2

CREATE TABLE dbo.Data(Value VARCHAR(10))

INSERT dbo.Data VALUES(DB_NAME())

GO

USE DB3

CREATE TABLE dbo.Data(Value VARCHAR(10))

INSERT dbo.Data VALUES(DB_NAME())

GO

In Working Db we are going to create SYNONYM dbo.Data table which is currently pointing to DB1 database:

USE WorkDB

GO

CREATE SCHEMA DB

GO

CREATE SYNONYM DB.Data FOR DB1.dbo.Data

Now we are going to create a stored procedure which will modify the database name of synonyms:

USE WorkDB

GO

  

CREATE PROC ChangeDbNameSynonym(

      @DbName AS SYSNAME

)

AS

BEGIN

      DECLARE @DyScript AS VARCHAR(MAX)

      SET @DyScript = ''

      SELECT

            @DyScript += 'DROP SYNONYM ' +QUOTENAME(SCHEMA_NAME([schema_id])) +  '.' + QUOTENAME(name)

                  + '; CREATE SYNONYM DB.' + QUOTENAME(name)

                  + ' FOR ' + REPLACE(base_object_name, QUOTENAME(PARSENAME(base_object_name,3)) + '.',@DbName+ '.') + ';'

      FROM sys.synonyms

      WHERE [schema_id] = SCHEMA_ID('DB')

      EXEC(@DyScript)

END

Finally we are going to create procedure which accept database name as parameter and fetch data from tables in different database without using dynamic SQL:

CREATE PROC FetchData(

      @DbName AS SYSNAME

)

AS

BEGIN

      EXEC dbo.ChangeDbNameSynonym @DbName

      SELECT * FROM DB.Data

END

  

We have done!! Now we are going to verify it.

EXEC dbo.FetchData 'DB1'

EXEC dbo.FetchData 'DB2'

EXEC dbo.FetchData 'DB3'

We will get three result set as below:

Value

DB1

Value

DB2

Value

DB3