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: 9493