Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Use Synonyms to Abstract the Location of SQL Server Database Objects

By:   |   Last Updated: 2009-08-20   |   Comments   |   Related Tips: More > Synonyms

I have an ETL process where the source and staging databases are on the same SQL Server instance.  Over time many stored procedures have been deployed to the staging database and these stored procedures access tables in the source database by using the three part name; i.e. [databasename].[schema].[tablename].  We are ready to move the staging database to a new server.  We want to update the stored procedures in staging so that they can access the tables in the source database whether the source database is on the same or a different SQL Server instance.  In other words we want to make a single change to the stored procedures that will allow them to work regardless of where the source database is deployed.   Do you have any ideas on how to do this? 

SQL Server 2005 introduced a new feature called synonyms.  Essentially a synonym allows you to specify an alias for a database object.  The short answer to your question is that you can create a synonym in the staging database for each table that you use in the source database, then change all of your stored procedures in staging to use the synonym instead of the three part name.  When your stored procedures use the synonym to reference the source database tables, you can then move the source database to a different server and all you have to do is change the definition of the synonym; you do not have to change the stored procedures. 


Let's restate the problem:

  • We have stored procedures in the staging database that access tables in the source database using three part names

  • We want to change the stored procedures to access the source tables via synonyms so that we don't have to change the stored procedures if we move the source database to another server

In the original problem statement, you noted that the stored procedures in staging are using three part names to access the tables in the source database.  If we move the source database to a different server, we can setup a linked server and use four part names to access the tables in the source database.  So what we want is to create a synonym that will abstract the use of the three or four part names.  We can create a T-SQL script that creates a synonym for a list of tables, using the 4 part name if a particular linked server exists, and using the three part name if we are not using a linked server. 

The T-SQL script is as follows: 

use mssqltips_staging
set nocount on;
declare @schema_name sysname
declare @cmd nvarchar(256)
-- STEP 1: create a schema for the synonyms
set @schema_name = N'oltp'
if not exists (
 select * from sys.schemas
 where name = @schema_name
 set @cmd = 
   N'create schema ' + 
   @schema_name + 
   N' authorization dbo'
  exec sp_executesql @stmt = @cmd 
-- STEP 2: get the schema id
declare @schema_id int
select @schema_id = schema_id 
from sys.schemas
where name = @schema_name;
-- STEP 3: create synonyms for list of tables
declare @table_list table (
 table_name sysname
declare @table_name sysname
declare @prefix nvarchar(50)
declare @synonym sysname
declare @linked_server sysname
set @linked_server = N'[L00458\sql2008]'
-- create a synonym for these tables (insert each table)
insert into @table_list (table_name) values ('customer')
insert into @table_list (table_name) values ('product')
if exists (
 select * from sys.servers
 where name = @linked_server
 -- 4 part name
 set @prefix = @linked_server + N'.' +  
               @schema_name + N'.dbo.'
 -- 3 part name
 set @prefix = @schema_name + N'.dbo.'
select top 1 @table_name = table_name 
from @table_list
while @table_name IS NOT NULL
 -- set the schema and name for the synonym
 set @synonym = @schema_name + N'.' + @table_name
 -- delete the synonym if it exists
 if exists (
  select * from sys.synonyms
  where name = @table_name
  and schema_id = @schema_id
  set @cmd = N'drop synonym ' + @synonym 
  exec sp_executesql @cmd
 -- create the synonym
 set @cmd = N'create synonym ' + 
            @synonym + 
            N' for ' + 
            @prefix + 
 print @cmd
 exec sp_executesql @cmd
 delete top (1) from @table_list
 set @table_name = null
 select top 1 @table_name = table_name 
 from @table_list

The main points about the above script are:

  • STEP 1: create the oltp schema in the staging database (if it doesn't already exist); all synonyms will be created in this schema.  This is not a requirement; the intent is just to isolate the synonyms from the other database objects.

  • STEP 2: retrieve the schema_id for the oltp schema; it will be used later.

  • STEP 3: create a synonym for each table in the @table_list table variable.  Drop the synonym if it exists before creating it.  Note that if a particular linked server exists the synonym will specify a four part name else it will specify a three part name.

You can take a look at what synonyms have been created by querying the system view sys.synonyms.  Run the above script before creating the linked server, then use this query to check the synonyms created:

  sc.name + '.' + sy.name as synonym_name 
, sy.base_object_name  
from sys.synonyms sy
join sys.schemas  sc on sc.schema_id = sy.schema_id
where sc.name = 'oltp'

You will see output like the following; note that the base object name reflects the three part name; i.e. the source database is on the same server as the staging database (assuming the source database name is oltp):

Now create the linked server by running a script like the following:

declare @linked_server sysname
set @linked_server = N'[L00458\sql2008]'
exec master.dbo.sp_addlinkedserver 
  @server = @linked_server
, @srvproduct=N'SQL Server'
exec master.dbo.sp_addlinkedsrvlogin 
  @rmtsrvname = @linked_server
, @useself = N'True'
, @locallogin = NULL
, @rmtuser = NULL
, @rmtpassword = NULL

Run the script again to create the synonyms, then run the script to view the synonyms that were created; note the base object name is now the four part name; e.g.:

After creating the synonyms, change all stored procedures to use the synonyms instead of the three part names.  If the source database is ever moved, create (or drop) the linked server, then run the T-SQL script to create the synonyms.  The script automatically drops the synonym if it exists before creating it.  This is done to enable switching between the three part and four part names (and vice versa).

Next Steps

Last Updated: 2009-08-20

get scripts

next tip button

About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools