Drop all constraints in a database
-
Hi all,
Where can I get a script that can drop all the constraints in a table? I will like to drop all the constraints in a couple of user databases so I can drop some tables so I don't know how to do this as I'm not grounded when it comes to T-SQL scripting...
Thanks in advance
-
hi Matey,
You can try to drop one by one by using the following code.
alter table [Table name]
drop constraint [constraint name]
cheers
sisi
-
Here's a script that will produce statements to drop all the constraints in a database.
select 'ALTER TABLE ' + cast(table_name as char(40)) + 'DROP CONSTRAINT '
+ cast(constraint_name as char(50)) + char(13)
from information_schema.constraint_table_usage
order by table_name, constraint_name
Greg
Greg
-
when you drop constraints in the database, you need to drop them in order, foreign key constraints first, then others. Here is the script, assumes tables are owned by [dbo]
-- Drop All Foreign Key Constraints
Begin
Declare C1 Cursor LOCAL DYNAMIC For
Select 'Alter Table [dbo].[' + object_name(parent_obj) + '] Drop Constraint ' + name
from dbo.sysobjects where ObjectProperty(id, 'IsForeignKey')=1
Declare @strStatement nvarchar(1000)
Open C1
Fetch C1 Into @strStatement
While @@FETCH_STATUS = 0
Begin
Execute (@strStatement)
Fetch C1 Into @strStatement
End
Close C1
Deallocate C1
End
Go
-- Drop Primary Key constraints
Begin
Declare C1 Cursor LOCAL DYNAMIC For
Select 'Alter Table [dbo].[' + object_name(parent_obj) + '] Drop Constraint ' + name
from dbo.sysobjects where ObjectProperty(id, 'IsPrimaryKey')=1
Declare @strStatement nvarchar(1000)
Open C1
Fetch C1 Into @strStatement
While @@FETCH_STATUS = 0
Begin
Execute (@strStatement)
Fetch C1 Into @strStatement
End
Close C1
Deallocate C1
End
Go
-- Drop all other constraints
Begin
Declare C1 Cursor LOCAL DYNAMIC For
Select 'Alter Table [dbo].[' + object_name(parent_obj) + '] Drop Constraint ' + name
from dbo.sysobjects where ObjectProperty(id, 'IsConstraint')=1
Declare @strStatement nvarchar(1000)
Open C1
Fetch C1 Into @strStatement
While @@FETCH_STATUS = 0
Begin
Execute (@strStatement)
Fetch C1 Into @strStatement
End
Close C1
Deallocate C1
End
Go
-
I've tested all the scripts posted above and they all work especially the last one that has the 3 steps that totally slipped my mind. Thanks a bunch people and hopefully I will not have any issues when this goes live...
-
The scripts work just fine, especially that of Vishwas Apte. Thank you guys.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply