Dropping all foreign keys

I would like to have a procedure that I can call that will drop all foreign
keys. I can get the constraint name from sysobjects if I select all fkey
constraints, but how can I get the associated table name so that I can plug
that into an alter table statement?
I appreciate any suggestions.
ThanksSee the first query at http://www.aspfaq.com/2520
Just change it from "SELECT FK_Table ... FROM" to the following:
SELECT 'ALTER TABLE '+FK.TABLE_NAME+' DROP CONSTRAINT '+C.CONSTRAINT_NAME
FROM
Run it in query analyzer and you will generate a script in the bottom pane,
which you can then copy and run in a new Query Analyzer window.
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks
Here is one that I wrote. It's not pretty and it's not optimized, but it
works.
Rick Sawtell
----
PRINT ''
PRINT ''
PRINT ''
PRINT '**************************************'
PRINT '* Dropping Foreign Key Constraints *'
PRINT '**************************************'
SET NOCOUNT ON
DECLARE @.TableNames TABLE(TableName nvarchar(256))
DECLARE @.TableCount int
DECLARE @.TableName nvarchar(256),
@.FKName nvarchar(256)
DECLARE @.FKNames TABLE(FKName nvarchar(256))
DECLARE @.FKCount int
INSERT @.TableNames
SELECT name
FROM sysobjects
WHERE TYPE = 'U'
AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
SELECT @.TableCount = Count(*) FROM @.TableNames
WHILE @.TableCount > 0
BEGIN
SELECT @.TableName = MIN(TableName)
FROM @.TableNames
SET @.FKName = NULL
INSERT @.FKNames (FKName)
SELECT name
FROM sysobjects
WHERE TYPE = 'F'
AND parent_obj = OBJECT_ID(@.TableName)
AND OBJECTPROPERTY(OBJECT_ID(name), 'IsForeignKey') = 1
SELECT @.FKCount = COUNT(*)
FROM @.FKNames
WHILE @.FKCount > 0
BEGIN
SELECT @.FKName = MIN(FKName)
FROM @.FKNames
PRINT ' Dropping Constraint ' + @.TableName + '.' + @.FKName
EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.FKName)
DELETE @.FKNames
WHERE FKName = @.FKName
SET @.FKCount = @.FKCount - 1
END
DELETE FROM @.TableNames WHERE TableName = @.TableName
SET @.TableCount = @.TableCount - 1
END|||WHY? Do you really want to sail through the windshield?
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
> I would like to have a procedure that I can call that will drop all
foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||*All* of them? I just hope some of them will make it back... someday.
ML

Drop all objects

HI,

Been poking around in sysobjects and information_schema.routines trying to work out how to best write scripts that will drop all specific objects from a database.

That is, scripts to drop all tables, views, stored procs, functions ( FN, IF, TF ) but can't seem to figure out appropriate way to do it.

A pointer on how to drop all of any one of the above object types would be greatly appreciated and I should be able to work out the others.

Further, when executing multiple scripts I am writing scripts like this...

ddl_batch.sql

Code Snippet

:R table1.sql

:R table2.sql

:R ufn_func1.sql

:R ufn_func2.sql

:R view_table1

:R view_table2

:R usp_proc1.sql

:R usp_proc2.sql

And executing via:

sqlcmd -S server\instance -i ddl_batch.sql

This is to maintain individual object type scripts, and then to execute them together in dependency order, rather than executing one monolithic batch script. Is this a reasonable way to go about it or is there a better way?

Many thanks in advance for your help.

Compose your SQL Strings on the fly using something like the following:

SELECT 'DROP TABLE [' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'),'IsMSShipped') = 0 --You do not want to drop the MS Shipped, right ;-) ?

You can use the output and store it in a file or execute it on the fly. Make sure that you first frop the foreign keys on the tables, then drop the tables, then the views, afterwards the functions.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Once again Jens many thanks for your help.