Delete data from all tables except master tables with foreign key contraints in SQL Azure

Question

I have 70+ tables in my azure sql database. I want to delete data from all these tables except some by bypassing the foriegn keys. Is there any script available to do the same because some table may have complex and long foreign key relationships.

I have gone through several links but not getting exact solution to automate this process.

Does anyone know how I could proceed?

Answer 1

Sample data:

Here I have Employee and Customer as master table and other tables are my child tables before truncating child table you need to drop the foreign key constraints otherwise you will face this error.

You can use dynamic SQL script to perform this sample script

--Get the list of all the tables to be truncated
 DECLARE @TablesToBeTruncated AS TABLE
 (Id INT IDENTITY(1,1),TableObjectId INT, TableName SYSNAME,
    SchemaId INT)
INSERT INTO @TablesToBeTruncated
 SELECT Sf.referenced_object_id ,So.name , so.schema_id
 FROM sys.foreign_keys Sf
 join sys.objects so on sf.referenced_object_id = so.object_id
 WHERE So.type = 'U'

 
 --Generate the foreignkeys drop
DECLARE @CreateScript AS NVARCHAR(MAX), @DropScript AS NVARCHAR(MAX)
SELECT
    ------------DROP SCRIPT--------------------
    @DropScript = ISNULL(@DropScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
     + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(FKey.name)
     + CHAR(10)
 FROM @TablesToBeTruncated Tlist
            INNER JOIN SYS.FOREIGN_KEYS FKey
                ON Tlist.TableObjectId = FKey.referenced_object_id
 
--PRINT THE TRUNCATION SCRIPT
IF LEN(ISNULL(@DropScript,'')) > 0
 BEGIN
     PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------DROP FOREIGN KEY CONSTRAINTS SCRIPT--------'
     PRINT @DropScript + CHAR(10) + ' GO ' + CHAR(10)
     EXEC(@DropScript)
 END
 
 
PRINT '--------TRUNCATE TABLES SCRIPT--------'
--TRUNCATE TABLES
DECLARE @id INT,@truncatescript NVARCHAR(MAX)
SELECT @id = MIN(Id)FROM @TablesToBeTruncated
WHILE @id is not null
 BEGIN
     SELECT @truncatescript = 'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(SchemaId)) + '.' + QUOTENAME(TableName) 
     FROM @TablesToBeTruncated WHERE Id = @id
     PRINT @truncatescript
     EXEC(@truncatescript)
     SELECT @id = MIN(Id)FROM @TablesToBeTruncated WHERE Id > @id
 END

Output: