TRUNCATE table only if it exists (to avoid errors)

Question

I have a .sql file that clear cached data in a mysql schema before export because the cached data in these tables is useless when I import the export file to a different server. I use this script on multiple sites, but in some instances certain table don't exist because they are not required. How do I only truncate a table in MySQL if only that table exist?

TRUNCATE accesslog;
TRUNCATE cache;
TRUNCATE cache_block;
TRUNCATE cache_bootstrap;
TRUNCATE cache_customfilter;
TRUNCATE cache_field;
TRUNCATE cache_filter;
TRUNCATE cache_form;
TRUNCATE cache_image;
TRUNCATE cache_menu;
TRUNCATE cache_metatag;
TRUNCATE cache_page;
TRUNCATE cache_path;
TRUNCATE cache_token;
TRUNCATE cache_update;
TRUNCATE cache_views;
TRUNCATE cache_views_data;
TRUNCATE watchdog;

Thanks in advance.

Mousterian

Either (1) live with the errors (2) create those tables everywhere, possible even in this .sql (create if not exists...) (3) write an little application that checks before truncate. – Petticoat

@Petticoat - cool. I thought you were being rude. But I see what you mean now. – Betti

@PreetSangha: well, you put me on a path, for which I thank you. I've commented on your answer what alterations need to be done to make it actually work. – Petticoat

Answer 1

See this answer on Using the IF Statement to do a conditional insert:

Can you do the same using the ANSI INFORMATION_SCHEMA

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'accesslog')
    TRUNCATE accesslog

Betti

Don't omit that (a) this needs to be in a function or procedure (b) you need a THEN & END IF (c) CALL the procedure and (d) DROP the procedure. – Petticoat

and if you have a second database with a table called accesslog, you're gonna have a bad time. – Vivienne

This is not SQL but from other comment "Stored program" – Preparative

Answer 2

Example table name is Salaries: before TRUNCATE u can check records(select * from Salaries) After truncate it will reset identity too.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Salaries')

begin

    TRUNCATE table Salaries;

end

Heliocentric

Answer 3

You can try this stored procedure as it is really difficult without it.

CREATE PROCEDURE tbl_delete(IN table_name CHAR(255),IN database_name CHAR(255))
BEGIN
    IF @table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = database_name)
    THEN
        SET @query = CONCAT("TRUNCATE TABLE ", @database_name, ".", @table_name);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
    END IF;
END ;

Hogen

Answer 4

CREATE PROCEDURE CheckAndTruncateTable @DatabaseName NVARCHAR(128), @SchemaName NVARCHAR(128), @TableName NVARCHAR(128), @Action NVARCHAR(10) -- 'CHECK' or 'TRUNCATE' AS BEGIN SET NOCOUNT ON;

DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE @RecordExists BIT = 0;

-- Validate the database name, schema, and table name to prevent SQL injection
IF EXISTS (
    SELECT * 
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.databases d ON d.database_id = DB_ID(@DatabaseName)
    WHERE t.name = @TableName AND s.name = @SchemaName
)
BEGIN
    -- Construct dynamic SQL to check for record existence
    SET @DynamicSQL = N'SELECT TOP 1 @RecordExistsOut = 1 FROM [' + @DatabaseName + '].[' + @SchemaName + '].[' + @TableName + ']'
    EXEC sp_executesql @DynamicSQL, N'@RecordExistsOut BIT OUTPUT', @RecordExists OUTPUT

    IF @Action = 'CHECK'
    BEGIN
        IF @RecordExists = 1
            SELECT 'Record exists in the table.' AS Message
        ELSE
            SELECT 'No records found in the table.' AS Message
    END
    ELSE IF @Action = 'TRUNCATE'
    BEGIN
        -- If a record exists, truncate the table
        IF @RecordExists = 1
        BEGIN
            SET @DynamicSQL = N'TRUNCATE TABLE [' + @DatabaseName + '].[' + @SchemaName + '].[' + @TableName + ']'
            EXEC sp_executesql @DynamicSQL
            SELECT 'Table truncated successfully.' AS Message
        ELSE
            SELECT 'No records found to truncate.' AS Message
        END
    END
    ELSE
    BEGIN
        SELECT 'Invalid action specified. Use ''CHECK'' or ''TRUNCATE''.' AS Message
    END
END
ELSE
BEGIN
    -- Handle invalid input
    RAISERROR('The specified database, schema, or table does not exist or the names are invalid.', 16, 1)
END

END

Telophase