How to truncate all tables in SQL Server.


To truncate all tables in SQL Server we need to create dynamic script.

DECLARE @TableName AS VARCHAR(1000) 
DECLARE @Script AS VARCHAR(1000) 
IF OBJECT_ID('tempdb.dbo.#AllTables') IS NOT NULL
    DROP TABLE #AllTables
 
SELECT name, ROW_NUMBER() over (order by name) as RNo
INTO #AllTables
FROM sys.tables

Declare @TotalTableCount int
Set @TotalTableCount =(Select Count(1) from #AllTables)
Declare @Counter int=1
WHILE (@Counter < @TotalTableCount)
    BEGIN
        SELECT @TableName = name
        FROM   #AllTables Where RNo = @Counter
        SELECT @Script = 'truncate table ' + @TableName;        
        EXECUTE (@Script)
        Set @Counter += 1
    END

3 thoughts on “How to truncate all tables in SQL Server.

  1. Hosting Deutschland

    Truncating all of the tables will only work if you don’t have any foreign key relationships between your tables, as SQL Server will not allow you to truncate a table with a foreign key.

Comments are closed.