Interview Question - Write the SQL script to delete/Truncate data from all the tables of the specific database

Recently, I came across this question where candidate had to write a query to empty all the tables from the specific database. Prima facie it feels like a cakewalk for the candidate but believe me it's not. Why not ? Because one must consider the relationships between the tables while writing the query for this question. It could be multilevel hierarchy that one needs to identify before even thinking about deleting the data from the child tables.

So the question gets divided into 2 parts 

• Identify the relationship hierarchy
• Start deleting the data from the bottom i.e from child tables to the parent tables

Now the real question, is it really necessary to identify the hierarchy between tables ? Isn't there any other way to perform this ? 

Well there might be many but the one that appealed me is the following one 

• Disable all the constraints for all the tables
• Delete data from all the tables
• Enable all the constraints that were disabled in the first step

Following statement can be used to disable all the constraints for a particular table

ALTER TABLE [table name]

NOCHECK CONSTRAINT ALL

And to enable all the constrains

ALTER TABLE [Table Name]

CHECK CONSTRAINT ALL

Let us create one test database along with tables with multilevel hierarchy 

CREATE DATABASE [TestDelete] 

GO

USE [TestDelete]

CREATE TABLE T1

(

Id INT UNIQUE,

NAme VARCHAR(10)

)

CREATE TABLE T2

(

num INT REFERENCES T1(id) UNIQUE,

class INT

)

CREATE TABLE T3

(

stdnum INT not null,

name VARCHAR(10) DEFAULT 'unknown',

address VARCHAR(100)  

)

CREATE TABLE T4

(

Teachid INT not null REFERENCES T2(num),

name VARCHAR(10) DEFAULT 'Mr.X'

)

INSERT INTO T1

SELECT 1,'chetan'

UNION ALL

SELECT 2,'akshay'

INSERT INTO T2

SELECT 1,12

INSERT INTO T3

SELECT 1,'asdjaks','adghjasbisdbc'

INSERT INTO T4(Teachid)

SELECT 1

SELECT * FROM T1

SELECT * FROM T2

SELECT * FROM T3

SELECT * FROM T4

--Now try running following statements one by one and observe the behavior 

-- Cannot perform TRUNCATE on the tables which are referenced by other tables

TRUNCATE TABLE T1

-- throws an error because this particular ID is referenced by T2

DELETE FROM T1 WHERE id = 1

-- Cannot perform TRUNCATE on the tables which are referenced by other tables

TRUNCATE TABLE T2

-- throws an error because this particular ID is referenced by T4

DELETE FROM T2 WHERE num = 1

--Disable constraint on the T2

ALTER TABLE T2

NOCHECK CONSTRAINT ALL

DELETE FROM T1 WHERE id = 1 -- now It worked

--To make it work for all the tables looping must be done and in this case sp_MsforEachTable comes handy

EXEC sp_MSforeachtable

'

ALTER TABLE ?

NOCHECK CONSTRAINT ALL

PRINT ''ALL THE CONSTRAINTS ARE DISABLED FOR '' + ''?''

'

PRINT CHAR(13) + CHAR(10)

EXEC sp_MSforeachtable

'

DELETE FROM ?

PRINT ''THE TABLE '' + ''?'' + '' HAS BEEN EMPTIED''

'

PRINT CHAR(13) + CHAR(10)

EXEC sp_MSforeachtable

'

ALTER TABLE ?

CHECK CONSTRAINT ALL

PRINT ''ALL THE CONSTRAINTS ARE ENABLED FOR '' + ''?''

'

If possible please try to achieve this using hierarchical approach and do let me know the solution that you come up with.