Delete data from all Tables in SQL Server

Whenever, the data is deleted or truncated from the tables in SQL Server, the only issue encountered is due to relationships between the tables. To overcome this issue, I have used below approach to delete the data from all the tables form a database in one go in SQL Server.

Steps

1. First identify all the tables having primary keys and store these tables name in a temp table.
2. Truncate all the tables excluding the tables name stored in temp table.
3. Now all the data from the referencing table has been deleted, so now we can delete all the data from the tables having Primary Key without any issue. So, delete or truncate all the tables that have Primary Key stored in temp table

DECLARE @QUERY NVARCHAR(MAX)
--===========================================
-- Getting all tables with primary key
--===========================================
SELECT OBJECT_NAME(ic.OBJECT_ID) as TBLNAME INTO #TEMP
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

--=========================================================
-- Truncate data from all tables without having Primary Key
--=========================================================
SET @QUERY = ''
SELECT    @QUERY = @QUERY + ';TRUNCATE TABLE ' + name
FROM    sysobjects
WHERE    xtype = 'U' AND name not in (SELECT TBLNAME FROM #TEMP)
EXEC sp_executesql @QUERY

--=========================================================
-- Delete data from tables with Primary Keys
--=========================================================
SET @QUERY = ''
SELECT    @QUERY = @QUERY + ';DELETE FROM ' + name
FROM    sysobjects
WHERE xtype = 'U' AND name in (SELECT TBLNAME FROM #TEMP)
EXEC sp_executesql @QUERY
DROP TABLE #TEMP