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.
- Views become stale and have to be updated if the underlying table changes, synonyms do not.
- 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:firstname.lastname@example.org 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]) &lt;&gt; 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;