Skip to content

Instantly share code, notes, and snippets.

@dongbum
Created February 26, 2020 08:50
Show Gist options
  • Save dongbum/d06f73d10bf5cd69db048c66925990d7 to your computer and use it in GitHub Desktop.
Save dongbum/d06f73d10bf5cd69db048c66925990d7 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment