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