Multi_table_truncate.sql

USE [GameDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE  PROCEDURE [dbo].[GSP_GD_TABLE_TRUNCATE]
	@confirm			NVARCHAR(1024)
,	@o_result			INT		OUTPUT

AS

	SET NOCOUNT ON
	SET LOCK_TIMEOUT 30000
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 	
	IF @confirm != N'truncate_confirm'
		RETURN

	SET @o_result = -1000;

	BEGIN TRAN
		DECLARE @TEMP_TABLE TABLE( seq int identity, table_name NVARCHAR(1024) )
	
		INSERT	@TEMP_TABLE
				SELECT	CONCAT('TRUNCATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
				FROM	information_schema.tables
				WHERE	TABLE_CATALOG = 'GameDB'

		DECLARE @i int, @j int
		SELECT @i = 1, @j = @@ROWCOUNT

		WHILE @i <= @j
		BEGIN
			DECLARE @sql NVARCHAR(1024) = (SELECT table_name FROM @TEMP_TABLE WHERE seq = @i)
			EXEC( @sql )

			SET @i = @i + 1
		END

		TRUNCATE TABLE [AccountDB].[dbo].[OtherTable]

	IF @@ERROR = 0
	BEGIN
		COMMIT TRAN
		SET @o_result = 0;
	END
	ELSE
	BEGIN
		ROLLBACK TRAN
		SET @o_result = @@ERROR
	END



GO