Jun 2

Written by: Markus Ehrenmüller-Jensen
Tuesday, June 02, 2009

Synonms are a great feature of SQL Server (introduced in version 2005). I’m using them in most of my projects for staying independent of database names (eg. when using a table stored in the staging area database in a procedure stored in the datawarehouse database).

Synonms work fine with SELECT, INSERT, UPDATE, DELETE and EXECUTE statements.

Unfortunatly this list does not contain TRUNCATE TABLE (which I prefer instead of DELETE because of performance reasons). But mixing synonyms and original names, using synonyms in some places, but original names in other places, would be a nonsens (I would get independent of database names in some places only, but not in others).

 

Here comes my workaround:

---

CREATE PROCEDURE [dbo].[truncate_table]

@synonym sysname

AS

set nocount on

declare     @stmt nvarchar(max)

select      @stmt='truncate table ' + base_object_name

from        sys.synonyms syn

            inner join sys.schemas sch on sch.schema_id=syn.schema_id

where       sch.name + '.' + syn.name=@synonym

--print @stmt

EXEC sp_executesql @stmt

GO

---

 

Instead of using TRUNCATE TABLE statement directly with my synonym (and receiving an error)

 

TRUNCATE TABLE sa.sales

Could not truncate object 'sa.sales' because it is not a table.

 

I replace it with a call to my procedure:

 

exec [dbo].[truncate_table] 'sa.sales'

 

As you can probably imagine: by modifing the content of variable @stmt you can do everything with a synonym you would be able to do with the underlying object.

Tags: