Noel Pulis

Tag: purge

  • SQL How to empty all tables in a database

    SQL How to empty all tables in a database

    You might need to purge all the data in all the tables in a particular database. For this we can use the following script.

    USE <database name>
    DECLARE @TableName AS VARCHAR(MAX)
    DECLARE table_cursor CURSOR
    FOR
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME LIKE '%_Partition%'
    OPEN table_cursor
    FETCH NEXT FROM table_cursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @SQLText AS NVARCHAR(4000)
    SET @SQLText = 'TRUNCATE TABLE ' + @TableName
    EXEC sp_executeSQL @SQLText
    FETCH NEXT FROM table_cursor INTO @TableName
    END
    CLOSE table_cursor
    DEALLOCATE table_cursor