Bolte IT Consulting

Truncate all tables in database

If you are confronted with the inconvenience of having to truncate all tables in a database in MS SQL Server, the following T-SQL might help.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
USE [db_name]
GO
/****** Object:  StoredProcedure [dbo].[truncate_tables]    Script Date: 17.11.2014 09:23:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<,,Alexander Bolte>
-- Create date: <2014-10-30,,>
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[truncate_tables]	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	declare tab_names cursor for
	SELECT TABLE_NAME 
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE' 
	declare @tab_name varchar(255)
 
	OPEN tab_names
	FETCH NEXT FROM tab_names
	INTO @tab_name		
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  EXECUTE ('Truncate table [' + @tab_name + '];');
	  FETCH NEXT FROM tab_names
	    INTO @tab_name
	END
	CLOSE tab_names
	DEALLOCATE tab_names
END

It is important to use the type varChar for the name @tab_name since its content is concatenated with some SQL command to truncate tables.

Hits: 9457