Drop indexes or alter

Question

I am trying to drop pk constraint and drop index on the target table in informatica, these below statements are working first time successfully.

IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'xyz') 
DROP INDEX [xyz] ON [dbo].[Table_Name];

IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'xyz')
ALTER TABLE [dbo].[Table_Name] DROP CONSTRAINT [xyz];

But if I run the same query a second time it is giving an error:

Cannot drop the index 'dbo.Table_Name.xyz', because it does not exist or you do not have permission

I need an If ... Else statement syntax like if exists drop else end or success something.

Answer 1

The likely cause for this is that you might have more than one index named xyz on your database. Your IF EXISTS SELECT statement is not taking into consideration which table xyz is on.

You can check for this condition yourself by running the select statement by itself.

Try changing your query to the following to limit the scope:

If Exists 
(
    Select * 
    From   sys.indexes 
    Where  name = 'xyz' 
    And    Object_Id = Object_Id('dbo.Table_Name')
)
Drop Index xyz On dbo.Table_Name;

Answer 2

One way to get around this issue is to trick the parser:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE SCHEMA_NAME = 'dbo' AND TABLE_NAME = 'Table_Name' AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME = 'xyz')
BEGIN
    EXEC('ALTER TABLE [dbo].[Table_Name] DROP CONSTRAINT [xyz]')
END