View foreign key constraints so I can delete a table

Question

I have copied a database I was working on so I could change some key elements of its design for my new version.

I want to delete totally some tables from the database however I cannot due to some foreign key constraints.

1) How can I view the foreign key constraints that exist on a table?

2) How can I delete the foreign keys and the table?

I am viewing this doc for 2008R2 but I am not understanding it sys.foreign_keys

Answer 1

And here's how to generate the script @Shark showed for all the tables you want to drop. Let's say you have the following tables:

USE tempdb;
GO

CREATE TABLE dbo.z(z INT PRIMARY KEY); -- we won't delete this one

CREATE TABLE dbo.a
(
 a INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.z(z)
);

CREATE TABLE dbo.b
(
 b INT PRIMARY KEY, 
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

CREATE TABLE dbo.c
(
 c INT PRIMARY KEY, 
 b INT FOREIGN KEY REFERENCES dbo.b(b), 
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

-- we won't drop this table either, but we'll need to drop
-- the constraint:

CREATE TABLE dbo.d
(
 d INT, 
 c INT FOREIGN KEY REFERENCES dbo.c(c)
);

But we only want to delete a, b, and c.

-- load the tables you want to delete into a table variable:

DECLARE @tables_to_delete TABLE (t NVARCHAR(512));

INSERT @tables_to_delete VALUES('dbo.a'),('dbo.b'),('dbo.c');


DECLARE @sql NVARCHAR(MAX) = N'';

-- build a list of the foreign keys you'll have to drop first:

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE ' 
    + QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(f.parent_object_id))
    + ' DROP CONSTRAINT ' + QUOTENAME(f.name) + ';'
FROM sys.foreign_keys AS f
INNER JOIN @tables_to_delete AS t
ON f.referenced_object_id = OBJECT_ID(t.t);

-- then the DROP TABLE commands:

SELECT @sql += CHAR(13) + CHAR(10) + N'DROP TABLE '
    + t + ';'
FROM @tables_to_delete;

PRINT @sql;
-- EXEC sp_executesql @sql;

Result (the constraint names will look different if you run this):

ALTER TABLE [dbo].[b] DROP CONSTRAINT [FK__b__a__2D27B809];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__a__30F848ED];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__b__300424B4];
ALTER TABLE [dbo].[d] DROP CONSTRAINT [FK__d__c__32E0915F];
DROP TABLE dbo.a;
DROP TABLE dbo.b;
DROP TABLE dbo.c;

When you're happy about the result, uncomment the EXEC line.

(Note, you won't be able to validate the script in its entirety when using PRINT if the script is very large. The script is truncated by Management Studio because it still has an archaic limit to how many characters it will show. The string won't be truncated like this when it gets passed to sp_executesql.)

Answer 2

This first query will get your foreign key constraints for your database, as well as the owning table and the referenced table:

select 
    name as FkName,
    object_name(parent_object_id) as ReferencingTable,
    object_name(referenced_object_id) as ReferencedTable
from sys.foreign_keys

If you want to drop a FK constraint, you'd do something like this:

alter table ChildTable
drop FK_ChildTable_RootTable_RootId
go

WARNING: Once you drop a foreign key constraint, that will destroy the referential integrity between the two tables. Do so carefully...

Answer 3

I'm assuming that you're trying to get rid of a table that's referenced by other tables (i.e., foreign key constraints exist in other tables that reference the table you're trying to remove).

To do this, right-click on the table in Object Explorer, and click the View Dependencies menu item.

The dialog box will show you which objects reference that table, including both schemabound and non-schemabound dependencies. You can then go to the referenced tables and remove the constraints.