How to add a variable to a drop statement

Hi there

I need to get a constraint name, this is what I'm using:

DECLARE @constraint as varchar(100)
set @constraint =
(select dobj.name
from sys.columns col
left outer join sys.objects dobj
on dobj.object_id = col.default_object_id and dobj.type = 'D'
where col.object_id = object_id(N'dbo.SongShare')
and dobj.name is not null)

and then I want to run this

ALTER TABLE MSO_SongShare DROP CONSTRAINT + ' ' + @constraint
alter table MSO_SongShare drop column share
EXEC sp_rename 'MSO_SongShare.share_New', 'share', 'COLUMN'
ALTER TABLE MSO_SongShare ADD  DEFAULT ((0)) FOR share

as you can see on this line

ALTER TABLE MSO_SongShare DROP CONSTRAINT + ' ' + @constraint

it does not work, how do i get the constraint name and drop it?

thanks
mousemat24Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Surendra NathConnect With a Mentor Technology LeadCommented:
you have  tse dynamic sql

declare @sql varchar(500)
set @sql = 'ALTER TABLE MSO_SongShare DROP CONSTRAINT ' + ' ' + @constraint
exec(@SQL)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.