Need to clear Database

Thouheed B 21 Reputation points
2021-06-18T06:26:15.01+00:00

In my Database I have a structure like:
106869-image.png

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

4 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-18T08:12:44.157+00:00

    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:
    106956-output.png

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

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

    106957-noerror.png

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

    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.

    0 comments No comments

  2. Viorel 111.5K Reputation points
    2021-06-18T11:40:33.097+00:00

    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.

    0 comments No comments

  3. Tom Phillips 17,716 Reputation points
    2021-06-18T13:58:55.047+00:00

    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/

    0 comments No comments

  4. Erland Sommarskog 100.7K Reputation points MVP
    2021-06-18T21:39:01.43+00:00

    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.

    0 comments No comments