Tag Archives: Delete or truncate all tables data within a database SQL Server

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