Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

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
0
mousemat24
Asked:
mousemat24
1 Solution
 
Surendra NathTechnology 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now