Download Sample
This article explains how to delete/truncate/drop without affecting schema properties (disabling constraints), even though tables have relations.
Why we need this
We can also use the below in-built method for deleting all tables.
EXEC sp_MSforeachtable @command1 = 'DELETE ?'
[But problem is: In TRUNCATE or DROP operations, SQL throws error if a table is referred in some other tables.]
If you want to drop or truncate a table that is referenced somewhere, you should get the following error.
“Could not drop object 'TableName' because it is referenced by a FOREIGN KEY constraint.”
Code
CREATE PROCEDURE udpFlushAllTablesByDBName
(
@DBName VARCHAR(200),
@FlushType VARCHAR(20)
)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT TOP 1 1 FROM SYS.TABLES WHERE OBJECT_ID = OBJECT_ID('tmpTable'))
BEGIN
DROP TABLE tmpTable
END
DECLARE @Query NVARCHAR(MAX);
SET @Query =
'WITH EliminateUnwanted (Name,[Object_ID],parent_object_id ,referenced_object_id) AS
(
SELECT
[T].Name,
[T].[Object_ID],
[F].parent_object_id,
[F].referenced_object_id
FROM ' + @DBName + '.SYS.TABLES [T]
LEFT JOIN ' + @DBName + '.SYS.FOREIGN_KEYS [F] ON [T].object_id = [F].parent_object_id
WHERE [T].Name NOT IN (''SysDiagrams'', ''tmpTable'') AND [T].TYPE = ''U''
),
SetPriority (Name,[Object_ID],parent_object_id ,referenced_object_id, parentObjectName, Priorities) AS
(
SELECT
*,
OBJECT_NAME(referenced_object_id) AS parentObjectName,
CASE
WHEN referenced_object_id IS NULL AND [Object_ID] NOT IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted ) THEN 1
WHEN [Object_ID] NOT IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted ) THEN 2
WHEN ([Object_ID] IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted ) AND parent_object_id IS NULL) THEN 4
ELSE 3
END ''PRIORITY''
FROM EliminateUnwanted
),
DuplicateRemoval (Occurence, Name,[Object_ID],parent_object_id ,referenced_object_id, parentObjectName, Priorities) AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS Occurence
,*
FROM SetPriority
)
SELECT
ROW_NUMBER() OVER(ORDER BY Priorities) RowNo,
Name,
Object_ID,
parent_object_id,
referenced_object_id,
parentObjectName,
Priorities
INTO tmpTable
FROM DuplicateRemoval
WHERE Occurence = 1'
--SELECT @Query
EXECUTE sp_executesql @Query
DECLARE
@TableName VARCHAR(100),
@Count BIGINT,
@FlushMethod VARCHAR(30);
SELECT @FlushMethod = CASE
WHEN @FlushType = 'TRUNCATE' THEN 'TRUNCATE TABLE '
WHEN @FlushType = 'DROP' THEN 'DROP TABLE '
ELSE 'DELETE '
END;
SET @Count = 1
WHILE EXISTS(SELECT TOP 1 1 FROM tmpTable WHERE RowNo = @Count )
BEGIN
SELECT @TableName = NAME FROM tmpTable WHERE RowNo = @Count
SET @Query = @FlushMethod + @DBName + '.dbo.' + @TableName
EXECUTE sp_executesql @Query
SET @Count = @Count + 1;
END
DROP TABLE tmpTable
SET NOCOUNT OFF;
END
How it functions
- Get all tables from given database
- Ordered by priorities
- Independent tables have high priority.
- Least tables from relations (child tables) have medium priority
- Referenced tables (parent) have low priority
- Truncate/Delete/Drop tables by priorities
