>>

>>DECLARE @CONSTRAINT_NAME nvarchar(128)
>>
>>SELECT @CONSTRAINT_NAME = CONSTRAINT_NAME
>>  FROM INFORMATION_SCHEMA.KEY_USAGE
>>  WHERE ...
>>
>>DECLARE @sql varchar(4000)
>>SET @sql = 'ALTER TABLE DROP CONSTRAINT ' + @CONSTRAINT_NAME
>>EXECUTE (@sql)
>>

>
>Inside a IF EXISTS condition, the syntax is not supported as is:
>
>

>DECLARE @Constraint_Name nvarchar(128)
>DECLARE @SQL varchar(4000)
>
IF (SELECT @Constraint_Name = Constraint_Name
> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> WHERE Table_Name = 'ArchiveRetrieval')
>if @@rowcount = 1
>   BEGIN
>      SET @SQL = 'ALTER TABLE ArchiveRetrieval DROP CONSTRAINT ' + @Constraint_Name
>      EXECUTE(@SQL)
>   END
>

>
>It says:
>
>"Incorrect syntax near '='."

SELECT @Constraint_Name = Constraint_Name
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE Table_Name = 'ArchiveRetrieval'
if @@rowcount = 1
...