Contents
How to truncate tables that have foreign keys?
Truncate Tables that have Foreign Keys 1 Save the Definition. The first thing we need to do is document all the foreign keys so we can re-create them later. 2 Drop the Constraints 3 Truncate the Tables. Using the undocumented stored procedure sp_msforeachtabled we can easily truncate all the tables now. 4 Re-Create the Constraints
How to get foreign and primary keys in SQL Server?
Create a temporary table and use this code to get the tables constraint and notice that the sysconstraints.status must be 1 or 3 to get foreign and primary keys: Select SysObjects. [Name] As “Constraint Name”, Tabls.
Is it possible to truncate all SQL Server Tables?
Truncating all of the tables will only work if you don’t have any foreign key relationships between your tables, as SQL Server will not allow you to truncate a table with a foreign key. An alternative to this is to determine the tables with foreign keys and delete from these first, you can then truncate the tables without foreign keys afterwards.
What is the purpose of a foreign key?
This is something you can obviously not do. The very purpose of foreign keys is to maintain referential integrity in your data. Imagine you have employees and departments with a FK from employees to departments. What this means is that an employee must always exist in a department.
Do you need an exclusive lock to truncate a table?
To drop or truncate a table you need an exclusive lock on the table and as Long as you don’t get the x-lock, your Statement has to wait. So it seems someone else is accessing the table preventing to grant a lock for you. Have you checked for blocking?
Which is better TRUNCATE TABLE or delete table?
Compared to the DELETE statement, TRUNCATE TABLE has the following advantages: Less transaction log space is used. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
Why does truncate or drop table take so long?
The seems to be the most likely reason for DROP/TRUNCATE taking a long time. Run sp_who2 and check the values in tbe Blk column. Any non-zero value in that column is the spid blocking the spid on that row. So check if any blocked spid is your TRUNCATE/DROP process.