Automate synonym creation

Let the code write the code.

We frequently want to access tables within another database on the same server. I often want to do this within a BI solution when the staging area is a separate database on the same server as the dimensional model database.

Never access these tables via 3-part names. If things change, say for example the staging database was moved to another server or to within the same database as the facts/dimensions you need to change your entire codebase where these tables are referenced.

My preferred solution here is to add synonyms as an abstraction layer. This is a very elegant solution, allowing you to move the base tables anywhere you like and for things to carry on working as before with a synonym change. An alternative here is to use views but synonyms are preferred for a number of reasons.

  1. Views become stale and have to be updated if the underlying table changes, synonyms do not.
  2. You can use hints on synonyms as if you were referencing the table itself, this is not supported with views.

I have automated the creation of such synonyms to produce a consistent repeatable structure for hundreds or even thousands of tables. Connect to the database containing the base tables to reference (the staging database in the example above) and run this script, this will produce statements to check for existence, drop, create synonym and also create an extended property to document who created the synonym and when. Take the output and run against your database that should reference the base tables (the dimensional model database in the example above).


/*
Name:

(C) Andy Jones

mailto:andrew@aejsoftware.co.uk

Description: -

Creates if exists / drop / create synonym statements for each table in a database.
Creates an extended property for each synonym with who created and when.

Change History: -
1.0 12/11/2015 Created.

License: free to download and use providing this header is kept in place. Author consent is required for
any sale or re-distibution.
*/
SET NOCOUNT ON;

WITH Command ( CommandID, Command )
AS ( SELECT 1 ,
'IF EXISTS (SELECT 1 FROM sys.synonyms WHERE [object_id] = OBJECT_ID(''_Replace_QualifiedName''))'
UNION ALL
SELECT 2 ,
CHAR(9) + 'BEGIN;'
UNION ALL
SELECT 3 ,
CHAR(9) + 'DROP SYNONYM _Replace_QualifiedName;'
UNION ALL
SELECT 4 ,
CHAR(9) + 'END;'
UNION ALL
SELECT 5 ,
CHAR(9) + ''
UNION ALL
SELECT 6 ,
'CREATE SYNONYM _Replace_QualifiedName FOR '
+ DB_NAME() + '._Replace_QualifiedName;'
UNION ALL
SELECT 9 ,
CHAR(9) + ''
UNION ALL
SELECT 10 ,
'EXEC sp_addextendedproperty @name = N''MS_Description'',@value = N'''
+ SYSTEM_USER + ' '
+ CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)
+ ': synonym pointing at ' + DB_NAME()
+ '._Replace_QualifiedName'',@level0type = ''SCHEMA'',@level0name = N''dbo'',@level1type = ''SYNONYM'',@level1name = N''_Replace_Name'', @level2type = NULL,@level2name = NULL;'
UNION ALL
SELECT 11 ,
CHAR(9) + ''
),
Obj ( QualifiedName, Name )
AS ( SELECT OBJECT_SCHEMA_NAME(o.object_id) + N'.' + o.name ,
o.name
FROM sys.objects AS o
WHERE o.type_desc IN ( N'USER_TABLE', N'VIEW' )
AND OBJECT_SCHEMA_NAME(o.[object_id]) <> N'tSQLt'
AND o.name NOT IN (N'sysdiagrams')
)
SELECT Command = REPLACE(REPLACE(c.Command, '_Replace_QualifiedName',
o.QualifiedName), '_Replace_Name',
o.Name)
FROM Command AS c
CROSS JOIN Obj AS o
ORDER BY o.QualifiedName ,
c.CommandID;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s