SQL Drop Index for different databases

Question

When trying to optimize SQL scripts, I was recommended to add indexes.

What is the easiest way to indicate in which database the pointer should be?

IF EXISTS (SELECT * FROM sysindexes WHERE NAME = 'idx_TableA') DROP INDEX TableA.idx_TableA IF EXISTS (SELECT * FROM sysindexes WHERE NAME = 'idx_TableB') DROP INDEX TableB.idx_TableB 

In the above code, TableA is in DB-A, and TableB is in DB-B.

When changing DROP INDEX TableA.idx_TableA to DROP INDEX DB-A.dbo.TableA.idx_TableA

The following error appears:

 Msg 166, Level 15, State 1, Line 2 'DROP INDEX' does not allow specifying the database name as a prefix to the object name. 

Any thoughts are appreciated.

Answer 1

Drop commands should use the USE statment with it if you intend to remove the index in the differet database.

 USE [DatabaseName] Drop Index [IndexName] 

Answer 2

If you have permissions, another way is with EXEC ('sql'). Also note that when requesting sys.indexes you need to specify a prefix with the database names (names):

 IF EXISTS (SELECT * FROM [DB-A].sys.indexes WHERE name = 'idx_TableA') EXEC('USE [DB-A]; DROP INDEX TableA.idx_TableA') IF EXISTS (SELECT * FROM [DB-B].sys.indexes WHERE name = 'idx_TableB') EXEC('USE [DB-B]; DROP INDEX TableB.idx_TableB') 

Answer 3

If you cannot issue a USE:

 EXEC [DB-A].dbo.sp_executesql N'DROP INDEX TableA.idx_TableA' 

Answer 4

You can also specify an index name.

 IF EXISTS (select 1 from sysindexes i, sysobjects o where o.name = 'idx_TableA' and o.id = i.id and i.name='indexname') exec('use [db-a]; drop index idx_TableA.indexname') SELECT 'Drop the index' else SELECT 'Index not found' 

Answer 5

Use the DROP INDEX ... ON syntax. This supports specifying the database and table name: http://msdn.microsoft.com/en-us/library/ms176118.aspx . The shorthand syntax you used is deprecated and will be removed in a future version of Microsoft SQL Server.

 IF EXISTS (SELECT * FROM [DB-A].sys.indexes WHERE NAME = 'idx_TableA') DROP INDEX [idx_TableA] ON [DB-A].dbo.[TableA] IF EXISTS (SELECT * FROM [DB-B].sys.indexes WHERE NAME = 'idx_TableB') DROP INDEX [idx_TableB] ON [DB-B].dbo.[TableB]