Drop a constraint using a variable

[sqlturbo]

Is it possible drop a constraint on a table by getting the constraint name in a variable and passing that variable to the drop constraint command. Here's an example.

DECLARE @name varchar(10)

SELECT @name = [name] from sysobjects Where xtype = 'F'
and Parent_obj = (SELECT OBJECT_ID('Table1'))

ALTER TABLE Table1
DROP CONSTRAINT @name

When I execute it, I get a "Incorrect syntax near '@name'." error on the Drop Constraint line.

Any ideas? Thanks for any input you may have.

 

[evaleah]

Try making the last part dynamic SQL.

Exec (ALTER TABLE Table1
DROP CONSTRAINT + '@name')

I actually learned that very thing from a helpful person on this forum! I would tell you who it was but I have forgotten now. I just wanted to make sure to give as much credit as I good where it belonged

 

[JamesLean]

evaleah is right but the syntax is not quite correct:

EXEC('ALTER TABLE table1 DROP CONSTRAINT ' + @name)

--James

 

[sqlturbo]

That worked. Thanks guys.