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
.)
You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id
for those object types.
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(s.name) + N'.'
+ QUOTENAME(t.name) + N' DROP CONSTRAINT '
+ QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];
PRINT @sql;
--EXEC sys.sp_executesql @sql;
PRINT
is just there for eyeballing, not for copying and pasting the output (if you want to run it, that's what the commented-out EXEC
is for) - if you have a lot of constraints, it may not show the entire script because PRINT
is limited to 4,000 characters (8kb). In those cases, if you need to validate the entire script, see this tip for other ways to validate the script before running. For example:
SELECT CONVERT(xml, @sql);
Once you are happy with the output, uncomment the EXEC
.
Best Answer
SQL Server prevents you from dropping the table if a foreign key exists,
nocheck
or not:Gives you:
Could not drop object 't2' because it is referenced by a FOREIGN KEY constraint
.So it's a moot point, really, you'll have to drop the FK first, either way.