drop all FKs in a database

I would like a script that will drop all FKs on all tables in a database.
ThanksIf it's just a one-off I would just build up the query batch to run with
this:
use mydb;
select 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP
CONSTRAINT [' + CONSTRAINT_NAME + ']'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY';
and then just copy & paste the results into another QA window and run it
(double check that you're running it in the correct DB!). If you need
to do this often to the same DB you can write a cursor wrapper around
this, execute the resultant T-SQL commands with sp_executesql in the
cursor loop and put the whole thing in a stored proc.
*mike hodgson*
http://sqlnerd.blogspot.com
Hassan wrote:

>I would like a script that will drop all FKs on all tables in a database.
>Thanks
>
>|||Mike, its not a one-off .
How can I use it so that I do not have to copy the results to another window
,etc... Could you help me with the cursor based query to exectute it in one
go ?
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:ea641TcNGHA.3360@.
TK2MSFTNGP15.phx.gbl...
If it's just a one-off I would just build up the query batch to run with thi
s:
use mydb;
select 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRA
INT [' + CONSTRAINT_NAME + ']'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY';
and then just copy & paste the results into another QA window and run it (do
uble check that you're running it in the correct DB!). If you need to do th
is often to the same DB you can write a cursor wrapper around this, execute
the resultant T-SQL commands with sp_executesql in the cursor loop and put t
he whole thing in a stored proc.
mike hodgson
http://sqlnerd.blogspot.com
Hassan wrote:
I would like a script that will drop all FKs on all tables in a database.
Thanks|||"Hassan" <Hassan@.hotmail.com> wrote in message news:
> Mike, its not a one-off .
> How can I use it so that I do not have to copy the results to another
> window,etc...
> Could you help me with the cursor based query to exectute it in one go ?
Look up help on cursors.
Michael|||Then just wrap it in a cursor and put it in a stored proc. Something
like this:
create proc DropAllForeignKeys
as
declare @.stmt nvarchar(200)
declare FKeys cursor
for
select N'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME
+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY'
for read only;
open FKeys;
fetch next from FKeys into @.stmt;
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
exec sp_executesql @.stmt;
end
fetch next from FKeys into @.stmt;
end
close FKeys;
deallocate FKeys;
go
*mike hodgson*
http://sqlnerd.blogspot.com
Hassan wrote:
> Mike, its not a one-off .
> How can I use it so that I do not have to copy the results to another
> window,etc... Could you help me with the cursor based query to
> exectute it in one go ?
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:ea641TcNGHA.3360@.TK2MSFTNGP15.phx.gbl...
> If it's just a one-off I would just build up the query batch to
> run with this:
> use mydb;
> select 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME +
> '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'
> from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where CONSTRAINT_TYPE = 'FOREIGN KEY';
> and then just copy & paste the results into another QA window and
> run it (double check that you're running it in the correct DB!).
> If you need to do this often to the same DB you can write a cursor
> wrapper around this, execute the resultant T-SQL commands with
> sp_executesql in the cursor loop and put the whole thing in a
> stored proc.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Hassan wrote:
>|||Hassan (Hassan@.hotmail.com) writes:
> Mike, its not a one-off .
Then I'm curious of what you really want to achieve. Dropping all FK as a
matter of routine would make me a little nervous. Maybe there are better
ways to do what you want to do.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||you may try this to disable all constraint temporarily
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
and can enable it again..
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
"Hassan" <Hassan@.hotmail.com> wrote in message
news:ei0$EEcNGHA.1312@.TK2MSFTNGP09.phx.gbl...
>I would like a script that will drop all FKs on all tables in a database.
> Thanks
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97705D84A1745Yazorman@.127.0.0.1...
> Hassan (Hassan@.hotmail.com) writes:
> Then I'm curious of what you really want to achieve. Dropping all FK as a
> matter of routine would make me a little nervous. Maybe there are better
> ways to do what you want to do.
I do this in my database when upgrading it on the end users machine. My app
will check the version of the database, if it is lower than it should be it
will drop all foreign keys, indexes, stored procs, functions etc, run the
necessary upgrades (eg, if it's on v6 but should be 9 it will run the 7, 8
and 9 upgrade code), then recreate all the foreign keys etc. It also drops
everything again between the different upgrades because sometimes the
upgrades need to create some objects.
Michael|||You do put the database in single-user mode for the duration of the upgrade,
don't you?
ML
http://milambda.blogspot.com/|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:FF673A35-4964-461D-B822-E037C1488F99@.microsoft.com...
> You do put the database in single-user mode for the duration of the
> upgrade,
> don't you?
No, we lock them out with a flag instead. I know we probably should use
exclusive mode but this is the way it was done. The problem I am finding now
with this method is that as the data gets bigger it's taking longer and
longer to recreate the indexes. I was thinking of having a way to mark
indexes that are not dropped or something like that.
Michael