SQL Drop Index on different Database

Question

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

What is the easiest way to specify what Database the index should be on?

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 code above, TableA is in DB-A, and TableB is in DB-B.

I get the following error when I change DROP INDEX TableA.idx_TableA to DROP INDEX DB-A.dbo.TableA.idx_TableA

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 must use a USE statment with it if you are going to drop an index on a differet Database.

USE [DatabaseName]
Drop Index [IndexName]

Answer 2

If you have permissions, another way is with EXEC('sql'). Also note that when querying sys.indexes, you need to prefix with the database name(s):

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')