Finding tables using foreign key constraint for a primary key of particular table.

Database cleanup is a on-going process for any application that has been live for many years. As updation and modification of existing objects in database is done on regular basis there are always some tables that are no more required. So as to perform a database cleanup we mostly delete these unwanted tables. But we are faced with a challenge to identify that in which tables the primary key of the unwanted table is being used as a foreign key constraint. Also another task is to identify the various stored procedures and views that might be using the table. In this post I am going to tell how to perform these tasks.

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'

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%'

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.