SQL Server 2008: Disabling all indexes for a synonym

Question

I have two identical tables, each mapped to a staging and production synonym. Our daily process is that staging is truncated, new data inserted, then we swap synonyms. To speed this up some, I'd like to disable all indexes on the staging synonym. The example in the link below is pretty close to what I need, but it's for tables not synonyms.

Disable all non-clustered indexes

The only method I've found to jump from the base object name in the synonyms table to getting the actual object_id of the base table in the objects table is using the replace() function in the script below. This seems decidedly dangerous coding, so I'm asking if there's another safer way that I'm missing.

select
    s1.name,
    s1.base_object_name,
    o1.object_id
from
    sys.synonyms s1
left join
    sys.objects o1
ON
    o1.name = replace(REPLACE(s1.base_object_name,'[database].[dbo].[',''),']','')
where
s1.name = 'synonym_name'

You know, now that I think of it my worry was if I were passing the synonym name as a parameter and there were duplicate object names in different databases, but I suppose I could solve that by requiring both a synonym name and a schema name as a parameter, then use the schema parameter to filter the objects list. — John, Jun 03 '14 at 14:47
Nice idea. Sometimes the right question helps you to think into the right direction. I believe your problem was solved and I think you should describe your idea above inside an answer and accept when allowed (after 2 days) to help people having the same problem in the future. — Lajos Arpad, Jun 03 '14 at 15:50

score 0 · Accepted Answer · answered Jun 09 '14 at 23:45

Below is the code I came up with and I hope should be fairly self-explanatory.

CREATE PROCEDURE synonym_index_disable
@db_name varchar(max),
@schema_name varchar(max),
@synonym varchar(max)

AS

DECLARE @sql AS VARCHAR(MAX)='';
DECLARE @fq_name varchar(max);

SET @fq_name = '[' + @db_name + '].[' + @schema_name + '].[';

SELECT @sql = @sql + 
'ALTER INDEX ' + sys.indexes.name + ' ON  ' + sys.objects.name + ' DISABLE;' +CHAR(13)+CHAR(10)
FROM 
    sys.indexes
JOIN 
    sys.objects
    ON sys.indexes.object_id = sys.objects.object_id
LEFT JOIN
    sys.synonyms
ON
    sys.objects.name = replace(REPLACE(sys.synonyms.base_object_name,@fq_name,''),']','')
WHERE
    sys.indexes.type_desc = 'NONCLUSTERED' AND
    sys.objects.type_desc = 'USER_TABLE' AND
    sys.synonyms.name = @synonym

EXEC(@sql);


GO

As stated in the question, this is a part of an ETL process where all indexes need to be dropped from a staging table. Staging/Production just synonyms and swap places after the ETL process is complete, so that's where this was useful for me. — John, Sep 29 '14 at 20:36

SQL Server 2008: Disabling all indexes for a synonym

1 Answers1