Solved

How to add a variable to a drop statement

Posted on 2013-06-24
1
451 Views
Last Modified: 2013-06-24
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
Comment
Question by:mousemat24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39271048
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question