C# Tutorials and offshore development in India

Prizes & Awards

My Profile

AdSense Revenue

Active Members
Today

Last 7 Days

more...

New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !

How to drop Constraints in SQL Server

Posted Date: 04 May 2008 Resource Type: Code Snippets Category: SQL

Posted By: @@@ Hyderabadi Biryani @@@ Member Level: Diamond
Rating:

Points: 15

The following code sample shows how to drop Constraints in SQL Server
declare @name nvarchar(32),
@sql nvarchar(1000)

-- find constraint name
select @name = O.name
from sysobjects AS O
left join sysobjects AS T
on O.parent_obj = T.id
where isnull(objectproperty(O.id,'IsMSShipped'),1) = 0
and O.name not like '%dtproper%'
and O.name not like 'dt[_]%'
and T.name = 'MyTable'
and O.name like 'DF__MyTable__MyColu%'

-- delete if found
if not @name is null
begin
select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']'
execute sp_executesql @sql
end

-- do your ALTER TABLE here

-- replace the constraint
select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]'
execute sp_executesql @sql

Responses

No responses found. Be the first to respond and make money from revenue sharing program.

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Import data directly from excel
	Previous Resource: How to check whether a Column exists in table if not then add it to the table
	Return to Discussion Resource Index
	Post New Resource
	Category: SQL

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design

it outsourcing