>>>First, thank you very much.
>>>When I run the above code from the VFP application, the line that DROPs INDEX, get this error message:
>>>

>>>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'?
>>
>>Sorry,
>>
>>DROP INDEX is separate command, not ALTER TABLE one ;-)
>>

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

>
>No need to be sorry; you are very helpful.
>Now I am running into another issue. The error is:
>

>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?

Yes, you should drop the constraint first.