Drop table in SQL Server

One of common query that we execute on day-to-day to basis is Dropping and re-creating a table when you are as beginner.

You can use DROP TABLE to drop table from database. But the table which you are trying to drop has child references then you can’t drop this table.

Let’s try to drop DEPT table.

By looking at the message you can’t really say what are all the other tables that have relationship with this table. So now the BIG task is to find out the tables which have relationship with this table.

The below SQL query will help you to find out the child tables associated with this DEPT table.

SELECT

TC.CONSTRAINT_NAME AS FOREIGN_KEY_CONSTRAINT_NAME,
TC.TABLE_NAME AS FOREIGN_KEY_TABLE_NAME,
KCU.COLUMN_NAME,
ORDINAL_POSITION AS COLUMN_POSITION,
RC.UNIQUE_CONSTRAINT_NAME AS PARENT_PRIMARY_KEY_NAME,
TC_PK.TABLE_NAME AS PARENT_TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC_PK

WHERE TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND RC.UNIQUE_CONSTRAINT_NAME = TC_PK.CONSTRAINT_NAME
AND TC_PK.TABLE_NAME = 'DEPT'