SQL Drop Index on different Database

Posted by Jim on Stack Overflow See other posts from Stack Overflow or by Jim
Published on 2010-06-01T18:07:00Z Indexed on 2010/06/01 18:13 UTC
Read the original article Hit count: 173

Filed under:
|
|

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 aboce, 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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2005