how to truncate a whole database(tables with too many referential keys) in sqlserver?

Question

I hope you guys could help me with solving the following problem.
I am new to SQL (using SQL Server Management Studio 2017).

My goals is to combine the keys of different tables into one table.
Which is shown below. For this example i have 4 source tables of which i only display the keys;

ContractTable:

+------------+---------+
| ContractId | PartyId |
+------------+---------+
| C01        | 1       |
+------------+---------+
| C02        | 2       |
+------------+---------+
| C03        | 3       |
+------------+---------+
| C04        | 4       |
+------------+---------+
| C05        | NULL    |
+------------+---------+

<!-- end snippet -->

PartyTable:

+---------+
| PartyId |
+---------+
| P1      |
+---------+
| P2      |
+---------+
| P3      |
+---------+
| P4      |
+---------+
| P5      |
+---------+
| P6      |
+---------+
| P7      |
+---------+
| P8      |
+---------+

RentalObjectTable:

+----------------+------------+----------+
| RentalObjectId | ContractId | ObjectId |
+----------------+------------+----------+
| R1             | C5         | O1       |
+----------------+------------+----------+
| R2             | NULL       | O2       |
+----------------+------------+----------+
| R3             | C4         | O3       |
+----------------+------------+----------+
| R4             | C1         | O4       |
+----------------+------------+----------+

ObjectTable:

+----------+
| ObjectId |
+----------+
| O1       |
+----------+
| O2       |
+----------+
| O3       |
+----------+
| O4       |
+----------+
| O5       |
+----------+
| O6       |
+----------+

Together they should form the Key/FactTable

+---------+------------+----------------+----------+
| PartyId | ContractId | RentalObjectId | ObjectId |
+---------+------------+----------------+----------+
| P1      | C01        | R4             | O4       |
+---------+------------+----------------+----------+
| P2      | C02        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P3      | C03        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P4      | C04        | R3             | O3       |
+---------+------------+----------------+----------+
| P5      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P6      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P7      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P8      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| NULL    | C5         | R1             | O1       |
+---------+------------+----------------+----------+
| NULL    | NULL       | R2             | O2       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O5       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O6       |
+---------+------------+----------------+----------+

Uptill now i tried the following:

CREATE TABLE FACTS(
ObjectId NVARCHAR(255),
RentalObjectId NVARCHAR(255),
ContractId NVARCHAR(255),
PartyId NVARCHAR(255)
);

INSERT INTO FACTS(ContractId)
SELECT ContractId
FROM ContractTable

UDATE FACTS
SET FACTS.Party_id = PartyTable.PartyId
FROM FACTS FULL OUTER JOIN
PartyTable on PartyTable.PartyId = FACTS.PartyId;

Using that same update statement for the other field.
However this does not work.
I do not get the values on which the query does not find a match.

Answer 1

use dbname
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

Credit to salil sir..

Answer 2

You can use the sp_MSforeachtable stored procedure like so:

USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Be warned that this will delete (by truncation) ALL data from all user tables. And in case you can't TRUNCATE due to foreign keys etc. you can run the same as a delete:

USE MyDatabase
EXEC sp_MSforeachtable 'DELETE FROM ?'

Credit: StackOverflow

Comments

Thanks for your answer.But,delete also doesn't work due to foreign key constraints...

---

Try this

EXEC sp_MSforeachtable 'PRINT ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'''
EXEC sp_MSforeachtable 'print ''DELETE FROM ?'''
EXEC sp_MSforeachtable 'print ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'''

After copy the printed result and paste it on Query field and Execute it. It should truncate all tables.

---

Try this also -

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

---

Thank you so much sir.I tried this one. it works perfectly...Thanks again..