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