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
|
No comments:
Post a Comment