>>>>Hi,
>>>>
>>>>The SQL Database has a constraint that I see (in SSMS) in Keys and Indexes. I need to remove this contraint and replace it with another.
>>>>
>>>>For example,
>>>>Constraint name IX_CATEGORY_UNIQUE is as follows:
>>>>

>>>>ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [IX_CATEGORY_UNIQUE] UNIQUE NONCLUSTERED 
>>>>(
>>>>	[CATEGORY] ASC
>>>>)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]
>>>>GO
>>>>

>>>>
>>>>I want to find it an replace with [SITE_NO],[CATEGORY]
>>>>
>>>>The first step is to find and drop it. What is the SQL Select to exececute from VFP?
>>>>
>>>>TIA
>>>
>>>

>>>TEXT TO lcSQL NOSHOW
>>>     ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [IX_CATEGORY_UNIQUE]
>>>ENTEXT
>>>IF SQLEXECT(sqlHandler, lcSQL)  < 0
>>>   AERROR(laError)
>>>   MessageBox(laError[1,2])
>>>   RETURN
>>>ENDIF
>>>
>>>TEXT TO lcSQL NOSHOW
>>>ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [IX_CATEGORY_UNIQUE] UNIQUE NONCLUSTERED 
>>>(
>>>	[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]
>>>ENDTEXT
>>>IF SQLEXECT(sqlHandler, lcSQL)  < 0
>>>   AERROR(laError)
>>>   MessageBox(laError[1,2])
>>>ENDIF
>>>

>>
>>Thank you very much for your code. I usually run this code on program update (which is done not often but still somewhat a repeat procedure). And I want to avoid having to run this code every time. So I would probably name the second constraint (on SITE_NO, CATEGORY) a little different. For example, IX_TABLE_CATEGORY_UNIQUE. But in this case, what happens when I run this code the 2nd time and the constraint IX_CATEGORY_UNIQUE does not exist and still the code tries to drop it. Then, there will be an error.
>>This is why I thought that before DROP the code would check if the constraint exist. Does it make sense?
>
>
>Is that index?
>
>

>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'ALTER TABLE dbo.MyTable DROP INDEX IX_CATEGORY_UNIQUE'
>      EXEC sp_executesql N'ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [IX_CATEGORY_UNIQUENEW] UNIQUE NONCLUSTERED 
>                          ([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....
>
>

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