by Bohumír Kubík
We have a scenario where more databases with the same schema are running on one SQL server instance (imagine the same application deployed for several customers with dedicated databases). We also needed to specify database name in stored procedures (in fact, we don’t have only one database per customer, but a pair of databases so that we have to specify the other database name).
The problem is that when you compare database schema among all of them, which is what you’ll be doing almost every time you need to make a change to the database, the compare tool will report differences in all procedures that specify database name.
For example, it treats with procedures like
SELECT * FROM Customer1_SecondDB.dbo.Table1;
SELECT * FROM Customer2_SecondDB.dbo.Table1;
as different. But we want to find differences only in the procedure’s logic.
Since SQL Server does not support database aliases, there are two possibilities:
1. Give up, and take care of changes by hand (at least make sure the database name is not changed by automatically generated change scripts). This is possible for two or three customers but will cost you a week to synchronize database for a dozen.
2. Make use of Synonyms. Although synonyms cannot be created for databases directly, we can still use it. The idea is that we create a synonym for every object in the database and then stored procedures will refer to those synonyms instead of fully qualified object names.
In Customer1_FirstDB we create synonym:
USE Customer1_FirstDB; CREATE SYNONYM dbo.SecondDB_Table1 FOR Customer1_SecondDB.dbo.Table1
In Customer2_FirstDB we create synonym:
USE Customer2_FirstDB; CREATE SYNONYM dbo.SecondDB_Table1 FOR Customer2_SecondDB.dbo.Table1;
The result is that in every database (Customer1_FirstDB, Customer2_FirstDB), procedures look like
SELECT * FROM dbo.SecondDB_Table1;
It is then very easy to find differences in schema among databases and also to create change scripts that will be able to upgrade all databases at once.
Creating a synonym for every object in the database might not seem to save a lot of time, but we need to make it only once. Moreover, you can generate the synonyms automatically.
I generate synonyms in next 3 steps. If you have more than 2 databases, you can modify it easy.
Table DBSynonym for storing a list of tables and views, that i want to create.
USE FirstDB GO CREATE SCHEMA [Admin] GO CREATE TABLE [Admin].[DBSynonym]( [SynonymName] [nvarchar](100) NOT NULL, [SchemaTable] [nvarchar](100) NOT NULL, CONSTRAINT [PK_DBSynonym] PRIMARY KEY CLUSTERED ( [SynonymName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
I use this script for easier filling table:
INSERT INTO FirstDB.Admin.DBSynonym ( SynonymName, SchemaTable ) SELECT '[' + TABLE_NAME + ']', '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' FROM SecondDB.INFORMATION_SCHEMA.TABLES
Stored procedure for generating synonyms:
CREATE PROCEDURE [Admin].[SetSynonyms] AS BEGIN DECLARE @SynonymName nvarchar(100) DECLARE @SchemaTable nvarchar(100) DECLARE @Command nvarchar(MAX) DECLARE @CommandPattern nvarchar(1000) = 'IF EXISTS (SELECT * FROM sys.synonyms WHERE name = REPLACE(REPLACE(''%%SYNONYM_NAME%%'', ''['', ''''), '']'', '''')) DROP SYNONYM SecondDBSchema.%%SYNONYM_NAME%% CREATE SYNONYM SecondDBSchema.%%SYNONYM_NAME%% FOR ' + 'SecondDB' + '.%%SCHEMA_TABLE%%' DECLARE cmd_cursor CURSOR FAST_FORWARD FOR SELECT SynonymName, SchemaTable FROM Admin.DBSynonym OPEN cmd_cursor FETCH NEXT FROM cmd_cursor INTO @SynonymName, @SchemaTable WHILE (@@FETCH_STATUS = 0) BEGIN SET @Command = REPLACE(REPLACE(@CommandPattern, '%%SYNONYM_NAME%%', @SynonymName), '%%SCHEMA_TABLE%%', @SchemaTable) EXEC(@Command) FETCH NEXT FROM cmd_cursor INTO @SynonymName, @SchemaTable END CLOSE cmd_cursor DEALLOCATE cmd_cursor END