Failed to delete object 'dbo.Table1' because it is referenced by FOREIGN KEY constraint
Even though I am deleting and trying to delete the table, I am getting the error,
ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [FK_Table1_Table2]
GO
DROP TABLE [dbo].[Table1]
GO
Mistake
Msg 3726, Level 16, State 1, Line 2 Could not delete object 'dbo.Table1' because it is referenced by FOREIGN KEY constraint.
Using SQL Server 2012
I generated a script using SQL Server 2012 so the sQL server gave me the wrong script?
source to share
Not sure if I understood correctly what you are trying to do, most likely Table1 is referred to as FK in another table.
If you do:
EXEC sp_fkeys 'Table1'
(this was taken from How can I list all foreign keys referencing a given table in SQL Server? )
This will give you all tables where the primary key "Table1" is FK.
Removing constraints that exist inside a table is not a necessary step to drop the table. Removing all possible FKs that refer to "Table 1".
As for the second part of your question, SQL Server automated scripts are blind for many reasons. Most likely the table preventing the deletion of table 1 is missing at all or not modified at all by the script. RedGate has several tools to help with these cascading deletes (usually when you are trying to dump a bunch of tables), but it's not bulletproof and quite expensive. http://www.red-gate.com/products/sql-development/sql-toolbelt/
source to share
First, you need to fold FK.
I can recommend that you look into this post, very interesting. It's called: SQL Foreign Key Constraint DROP TABLE
There is a good explanation on how to do this.
I will give the answer:
..... Doesn't drop your table if there are indeed foreign keys referencing it.
To get all foreign key relationships referencing your table, you can use this SQL (if you are on SQL Server 2005 and above):
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')
SELECT
'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) +
'.[' + OBJECT_NAME(parent_object_id) +
'] DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')
source to share
You can use these queries to find all FKs in your table and find FKs in tables that use your table.
Declare @SchemaName VarChar(200) = 'Your Schema name'
Declare @TableName VarChar(200) = 'Your Table Name'
-- Find FK in This table.
SELECT
' IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' +
OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +
'ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
-- Find the FKs in the tables in which this table is used
SELECT
' IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' +
OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +
' ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
source to share