CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60731

dynamically change synonym definitions

We have a database where we have approximately 100 synonyms that look like this:

 IF EXISTS (SELECT 1 FROM sys.objects Where type = 'SN' and name = 'CRM_ActivityPointerBase')
    DROP SYNONYM CRM_ActivityPointerBase
CREATE SYNONYM CRM_ActivityPointerBase FOR CRMPRODDB.TRAINING_MSCRM.dbo.ActivityPointerBase
GO 
 IF EXISTS (SELECT 1 FROM sys.objects Where type = 'SN' and name = 'CRM_AnnotationBase')
    DROP SYNONYM CRM_AnnotationBase
CREATE SYNONYM CRM_AnnotationBase FOR CRMPRODDB.TRAINING_MSCRM.dbo.AnnotationBase
GO
IF EXISTS (SELECT 1 FROM sys.objects Where type = 'SN' and name = 'CRM_SalesorderBase')
    DROP SYNONYM CRM_SalesOrderBase
CREATE SYNONYM CRM_SalesOrderBase FOR CRMPRODDB.TRAINING_MSCRM.dbo.SalesOrderBase
GO
IF EXISTS (SELECT 1 FROM sys.objects Where type = 'SN' and name = 'CRM_BusinessUnitBase')
    DROP SYNONYM CRM_BusinessunitBase
CREATE SYNONYM CRM_BusinessUnitBase FOR CRMPRODDB.TRAINING_MSCRM.dbo.BusinessUnitBase
GO

IF EXISTS (SELECT 1 FROM sys.objects Where type = 'SN' and name = 'CRM_SalesorderExtensionBase')
    DROP SYNONYM CRM_SalesOrderExtensionBase
CREATE SYNONYM CRM_SalesOrderExtensionBase FOR CRMPRODDB.TRAINING_MSCRM.dbo.SalesOrderExtensionBase
GO

Sometimes I need ALL the synonyms to point to CRMPRODDB.TRAINING_MSCRM and sometimes I need ALL the synonyms to point to ANOTHERSERVER.myDATABASE.

My idea is to create a function that I run that will just update every synonym.

Can someone help me get started?

I'm thinking of something like this:

declare @myvar varchar
select @myvar ='CRMPRODDB.TRAINING_MSCRM'

IF EXISTS (SELECT 1 FROM sys.objects Where type = 'SN' and name = 'CRM_ActivityPointerBase')
    DROP SYNONYM CRM_ActivityPointerBase
EXECUTE ('CREATE SYNONYM CRM_ActivityPointerBase FOR ' + @myvar);

But I would really like your guidance on the standard practice of doing this.

Thank you so much for your help.

Upvotes: 1

Views: 428

Answers (1)

SqlACID
SqlACID

Reputation: 4014

I would look at database projects, they can be a bit cumbersome to set up, but can do exactly this, your definition would look like this:

CREATE SYNONYM CRM_SalesOrderExtensionBase FOR $(server).$(database).dbo.SalesOrderExtensionBase

and you choose publish profiles to substitute the variables. They are just SQLCMD scripts under the covers, and you could do the same thing without a database project as well.

Upvotes: 1

Related Questions