1. Finding tables that use foreign key constraint on the primary key of the table
First lets identify all the tables that are using the foreign key constraint for the primary key of the unwanted table.The following query will easily list out all these tables along with the foreign-key constraint name:
SELECT FK_Table = FK.TABLE_NAME
, FK_Column = CU.COLUMN_NAME
, PK_Table = PK.TABLE_NAME
, PK_Column = PT.COLUMN_NAME
, Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME = 'Table_Name'
, FK_Column = CU.COLUMN_NAME
, PK_Table = PK.TABLE_NAME
, PK_Column = PT.COLUMN_NAME
, Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME = 'Table_Name'
Here Table_Name is the name of the table which needs to be deleted. While running this query just replace the Table_Name with your table name.
For example I want to find out the tables which are using the primary key of table F_Fees as foreign key constraint then on running the above query we would get the output as follows:
Here FK_Table column is having the names of the tables which is having the primary key constraint.
FK_Column is having the name of the column that is having the primary key constraint.
PK_Table is the name of the table that needs to be deleted and PK_Column is the name of the column which is referenced as foreign key in other tables.
Constraint_Name is the name of the constraint.
Hence by using this query one can easily find out the various foreign key constraints that are present which relates to the primary key of the table that needs to be deleted.
2. Identifying all the stored procedure and views that might be using the unwanted table
In order to identify all the stored procedures and views that might be using the unwanted table use the following query:
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o
ON c.id=o.id
WHERE c.TEXT LIKE '%Table_Name%'
FROM syscomments c
INNER JOIN sysobjects o
ON c.id=o.id
WHERE c.TEXT LIKE '%Table_Name%'
This query will return all the stored procedure and views that are using the particular table. For example if we run this query to find a particular table then its result would be as follows:
Here name denotes the name of the database object found and xtype refers to the type of database object. P denotes stored procedure and V denotes view.
Once identified delete all such foreign constraints, stored procedures, views etc and proceed with dropping the table for cleaning up the database.
