How do you truncate all tables except lookup tables in a database using TSQL?

Question

What is the best way to remove all the data from all the tables except look-up tables data using TSQL in SQL Server 2012 and downwards? I would like the TSQL identify and exclude look-up tables then create truncate table statements for the other tables.

** There is almost a similar question but it truncates all the tables.

Answer 1

Both Lookup Tables and non Lookup tables are similar in technical characteristics. Only functionally they are different. Hence there won't be specific criteria to differentiate both of them.

Answer 2

Unless you set yourself up to be able to do this from the design standpoint, e.g. putting all "Lookup" tables in a "lkup" schema, or something of that nature, I don't think there's a way to do this. As someone already mentioned, a lookup table is a table like any other.

Answer 3

I would automate using DELETE by deleting in the right order first of all (dependencies)

1) pass the table name

2) disable your foreign keys

3) empty DELETE ALL the table

4) re-enable they keys.

this way you can control passing the table names you want "truncated" with a conditional.

Answer 4

or will it matter then:

ALTER PROCEDURE 
up_ResetEntireDatabase 
@IncludeIdentReseed BIT, 
@IncludeDataReseed BIT 
AS 

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
EXEC sp_MSForEachTable 'DELETE FROM ?'

 IF @IncludeIdentReseed = 1 
BEGIN 
EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'' , RESEED, 1)' 
END 

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

IF @IncludeDataReseed = 1 
BEGIN 
-- Populate Core Data Table Here 
END 

GO

And then once ready the execution is really simple:

EXEC up_ResetEntireDatabase 1, 1

Answer 5

I'm not sure if you mean lookup tables like one that could drive this.

build a simple table that has names of each of the database tables and create columns you could modify if necessary before you execute the script.

the columns could just be flags that tell the script whether or not to truncate that table or other.

that way you (script) will know dependencies as it reads table names. an index is not needed if you keep table order static in record number order.

just another maintenance script.

Answer 6

So you want to truncate the tables that have foreign keys but keep the references tables alone. This should do it.

WITH CTE_fks
AS
(
    SELECT  obj.name AS FK_NAME,
            sch1.name AS [table_schema],
            tab1.name AS [table_name],
            col1.name AS [column],
            sch2.name AS [ref_table_schema],
            tab2.name AS [referenced_table],
            col2.name AS [referenced_column]
    FROM sys.objects obj
    INNER JOIN sys.foreign_key_columns fkc
        ON obj.object_id = fkc.constraint_object_id
    INNER JOIN sys.tables tab1
        ON tab1.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas sch1
        ON tab1.schema_id = sch1.schema_id
    INNER JOIN sys.columns col1
        ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
    INNER JOIN sys.tables tab2
        ON tab2.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas sch2
        ON tab2.schema_id = sch2.schema_id
    INNER JOIN sys.columns col2
        ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
)

SELECT  'TRUNCATE TABLE ' + QUOTENAME(A.TABLE_SCHEMA) + '.' + QUOTENAME(A.table_name) + ';'
FROM INFORMATION_SCHEMA.TABLES A
LEFT JOIN CTE_fks B
ON A.TABLE_NAME = B.referenced_table
AND A.TABLE_SCHEMA = B.ref_table_schema
WHERE A.TABLE_TYPE = 'BASE TABLE'
AND A.TABLE_NAME != 'sysdiagrams'
AND B.table_name IS NULL