>>>Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. >>>>>>Where do I need to add the 'WITH'?
>>TEXT TO lcSQL NOSHOW >>IF EXISTS(SELECT * FROM sys.indexes WHERE OBJECT_name(object_id) ='MyTable' AND Name = 'IX_CATEGORY_UNIQUE') >> BEGIN >> EXEC sp_executesql N'DROP INDEX IX_CATEGORY_UNIQUE ON dbo.MyTable' >> EXEC sp_executesql N'CREATE UNIQUE NONCLUSTERED INDEX [IX_CATEGORY_UNIQUENEW] ON dbo.MyTable >> ([SITE_NO],[CATEGORY])WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, >> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' >> END >>ENDTEXT >>sqlexec.... >>>
>An explicit DROP INDEX is not allowed on index 'dbo.MyTable.IX_CATEGORY_UNIQUE'. It is being used for UNIQUE KEY constraint enforcement. >>I think the problem is that this IX_CATEGORY_UNIQUE is both an index and is a unique key constraint. Does it mean that I have to DROP the constraint first, before dropping the index?