How To #5 - Truncate all/multiple tables in a database

You can use this script to truncate tables:

When you want to clean all tables in a database/schema but keep the design and not recreate the objects
During the initial testing period or early stages of a database design

Be very careful if using any form of this script against a production database.

USE databasename;

DECLARE @Table NVARCHAR(50);
DECLARE @Schema NVARCHAR(10);
DECLARE @SQLCommand NVARCHAR(250);

DECLARE Table_Cursor CURSOR FOR
SELECT TABLE_SCHEMA
      ,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
-- Select only the tables
WHERE TABLE_TYPE = 'BASE TABLE'
-- Use it if you need to filter on certain schema
AND TABLE_SCHEMA IN ('dbo')
-- Use it if you need to skip some of the tables
AND TABLE_NAME NOT IN ('RandomTable');

OPEN Table_Cursor;

FETCH NEXT FROM Table_Cursor INTO @Schema, @Table
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SELECT @SQLCommand = 'TRUNCATE TABLE [' + @Schema + '].[' + @Table + '];';
		EXEC (@SQLCommand);
		FETCH NEXT FROM Table_Cursor INTO @Schema, @Table;
	END;

CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;

USE databasename;

DECLARE @Table NVARCHAR(50);

DECLARE @Schema NVARCHAR(10);

DECLARE @SQLCommand NVARCHAR(250);

DECLARE Table_Cursor CURSOR FOR

SELECT TABLE_SCHEMA

,TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

-- Select only the tables

WHERE TABLE_TYPE = 'BASE TABLE'

-- Use it if you need to filter on certain schema

AND TABLE_SCHEMA IN ('dbo')

-- Use it if you need to skip some of the tables

AND TABLE_NAME NOT IN ('RandomTable');

OPEN Table_Cursor;

FETCH NEXT FROM Table_Cursor INTO @Schema, @Table

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT @SQLCommand = 'TRUNCATE TABLE [' + @Schema + '].[' + @Table + '];';

EXEC (@SQLCommand);

FETCH NEXT FROM Table_Cursor INTO @Schema, @Table;

END;

CLOSE Table_Cursor;

DEALLOCATE Table_Cursor;

How To #5 – Truncate all/multiple tables in a database

Leave a Reply Cancel reply