Technical Article

Truncate all tables in a database

,

This script creates a temp table of all the tables in a database and loops through a cursor to truncate them.

DECLARE @table varchar(64),
@sql varchar(150)
DECLARE curTable SCROLL CURSOR FOR SELECT name FROM sysobjects WHERE Type = 'U'  And Name <> 'dtproperties' ORDER BY name

OPEN curTable

FETCH FIRST FROM curTable INTO @table

WHILE (@@FETCH_STATUS = 0)
BEGIN
set @sql = 'truncate table ' + Cast(@table as varchar) 
--print @sql
exec (@sql)
    
   FETCH NEXT FROM curTable INTO @table
END
CLOSE curTable
DEALLOCATE curTable

Rate

4 (1)

Share

Share

Rate

4 (1)