How to truncate all table in a SQL SERVER database

SET NOCOUNT ON;

DECLARE @TBL TABLE (
       rowNum INT identity(1, 1)
       ,table_name NVARCHAR(100)
       )
DECLARE @counter INT
       ,@count INT

INSERT INTO @TBL
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

SELECT @count = @@rowcount
       ,@counter = 1

SELECT *
FROM @TBL

WHILE (@counter <= @count)
BEGIN
       DECLARE @tblName NVARCHAR(100)
              ,@sql1 NVARCHAR(256)

       SELECT @tblName = table_name
       FROM @TBL
       WHERE rowNum = @counter

       SET @sql1 = 'TRUNCATE TABLE ' + @tblName

       PRINT (@sql1)

       PRINT 'GO'

       SET @counter = @counter + 1

END

Post a Comment

Please Select Embedded Mode To Show The Comment System.*

Previous Post Next Post

Contact Form