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