How do you drop all constraints on a table in Microsoft SQL?

For various applications I have needed to drop all constraints on a given table, so I developed this tool that I have often used.  This tool however does NOT save the SQL to recreate the constraints.  So if you are looking to unshackle a table from all of its constraints you might try this.





-- Author: Thomas Ross RosSQL RosSQL.Blogspot.com
-- Date: 5/30/2012
-- Desc: Drop all constraints on a given table.
-- Instructions: Change the name of the table on the line given.
-- Turn the Safety OFF


DECLARE @Safety datetime
set @Safety='5/30/2012' --<<***** You MUST set this to today's date to modify the database.

declare @TblName varchar(1024); set @tblName = 'ApptSchedule' --<<***** Set to '' to see a complete list or choose a table name.
declare @st varchar(max)
set @st=''

select so.name as TableName, def.name as [These Constraints Dropped]
from sys.columns SC
join sysobjects so on so.id=sc.object_id
join sysobjects def on def.id=sc.default_object_id
where so.name=@TblName or @TblName = ''

select @st = @st + 'alter table ['+so.name+'] Drop Constraint [' +def.name+'];'
from sys.columns SC
join sysobjects so on so.id=sc.object_id
join sysobjects def on def.id=sc.default_object_id
where so.name=@TblName or @TblName = ''

if (@Safety between getdate()-1 and getdate()+1)
begin
select @st
exec (@st)
end
else
select 'Constraints NOT dropped because SAFETY is ON.' as [@Safety]

(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.