Need to clear Database

Question

In my Database I have a structure like:

I have prepared a dynamic store procedure to delete records from the DB but I am not getting the proper table order to delete records, That's why I am getting foreign key constraint error.

CREATE PROCEDURE [master].[DEL_RECORD_FRM_ALL_TABLE]
AS
DECLARE @V_TABLE_NAME VARCHAR(36)
DECLARE @V_FK_TABLE_NAME VARCHAR(36)
DECLARE @V_LEF_TABLE_NAME VARCHAR(36)
DECLARE CUR_FK CURSOR FOR
select a.name CHILD
from sysforeignkeys fk
join sys.objects a on a.object_id = fk.fkeyid
join sys.schemas b on a.schema_id=b.schema_id and b.name='MASTER'
where TYPE_DESC= 'USER_TABLE'
DECLARE CUR_ALL CURSOR FOR

	SELECT  A.NAME FROM sys.objects a  
	join sys.schemas b on a.schema_id=b.schema_id and b.name='MASTER'  
	where TYPE_DESC= 'USER_TABLE' and a.name not in(select a.name CHILD  
	from sysforeignkeys fk  
	join sys.objects a on a.object_id = fk.fkeyid  
	join sys.schemas b on a.schema_id=b.schema_id and b.name='MASTER'  
	where TYPE_DESC= 'USER_TABLE')   

OPEN CUR_FK
FETCH NEXT FROM CUR_FK INTO @V_FK_TABLE_NAME
WHILE @@Fetch _STATUS = 0
BEGIN
DECLARE CUR_LEF CURSOR FOR
SELECT OBJECT_NAME(fk.parent_object_id)
FROM sys.tables t INNER JOIN sys.foreign_keys fk
ON t.object_id = fk.referenced_object_id
WHERE t.name = @V_FK_TABLE_NAME
DECLARE @COUNT_RECORDS INT=(SELECT COUNT(1)
FROM sys.tables t INNER JOIN sys.foreign_keys fk
ON t.object_id = fk.referenced_object_id
WHERE t.name = @V_FK_TABLE_NAME),
@COUNT INT=1
IF(@COUNT<=@COUNT_RECORDS)
BEGIN
OPEN CUR_LEF
FETCH NEXT FROM CUR_LEF INTO @V_LEF_TABLE_NAME
WHILE @@Fetch _STATUS = 0
BEGIN
DECLARE @LF NVARCHAR(50)
SET @LF = 'DELETE FROM MASTER.'+ @V_LEF_TABLE_NAME
EXECUTE sp_executesql @LF
FETCH NEXT FROM CUR_LEF INTO @V_LEF_TABLE_NAME
SET @COUNT= @COUNT+1
END
CLOSE CUR_LEF
DEALLOCATE CUR_LEF
END

 ELSE  
	BEGIN  
		DECLARE @SQL NVARCHAR(50)  
		SET @SQL= 'DELETE FROM MASTER.'+@V_FK_TABLE_NAME  
		EXECUTE sp_executesql @SQL  
		FETCH NEXT FROM CUR_FK INTO @V_FK_TABLE_NAME  
	END  
END  

CLOSE CUR_FK
DEALLOCATE CUR_FK

OPEN CUR_ALL
FETCH NEXT FROM CUR_ALL INTO @V_TABLE_NAME
WHILE @@Fetch _STATUS = 0
BEGIN
DECLARE @all _TABLE NVARCHAR(50)
SET @all _TABLE= 'DELETE FROM MASTER.'+@V_TABLE_NAME
EXECUTE sp_executesql @all _TABLE
FETCH NEXT FROM CUR_ALL INTO @V_TABLE_NAME

		END  

DEALLOCATE CUR_ALL

Answer 1

Hi @Thouheed B ,

Welcome to Microsoft Q&A!

Per my knowledge, you could try with the opposite of order to delete the records which means that you have to delete leaf->child->parent.

I tried with AdventureWorks2017 database as below.

There is one FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID between Sales.SalesOrderHeaderSalesReason and Sales.SalesReason tables.

Their records are like below:

If I delete the data from Sales.SalesReason firstly, I would face the foreign key constraint error.

But if I delete the data from Sales.SalesOrderHeaderSalesReason firstly, I would not face any error.

And then you could delete the related data from Sales.SalesReason secondly without any error.

Hope above could be a little helpful to you.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Maybe you can avoid complex analysis of table relationships, and just delete the tables in any order until all of operations succeed.

Check an initial conceptual script:

declare C scroll cursor for 
    select name from sys.tables where name <> 'sysdiagrams'

open C

while 0 = 0
begin

    declare @retry bit = 0
    declare @tablename varchar(max)

    fetch first from C into @tablename

    while @@FETCH_STATUS = 0
    begin

        declare @sql varchar(max) = concat('delete from ', quotename(@tablename))

        exec (@sql)

        if @@ERROR <> 0 set @retry = 1

        fetch next from C into @tablename

    end

    if @retry = 0 break
end

close C deallocate C

However, see other approaches too: https://www.bing.com/search?q=sql+server+%28delete+OR+truncate%29+all+tables.

Answer 3

The simplest answer to your question is to set your FKs to "DELETE CASCADE". Then the engine handles that all for you.

https://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

Answer 4

When I do when I need to clear a database is simply to do:

SELECT 'DELETE ' + quotename(s.name) + '.' + quotename(o.name) +
            char(13) + char(10) + 'go' + char(13) + char(10)
FROM   sys.tables o
JOIN  sys.schemas s ON o.schema_id + s.schema_id

Then I copy the result to a query window and I run it multiple times until there are no error messages.