Check if a temporary table exists and delete if it exists before creating a temporary table

Safely Manage SQL Server Temporary Tables: Check, Drop, and Create

Learn robust methods to check for the existence of a temporary table in SQL Server and conditionally drop it before recreation, preventing errors and ensuring clean execution.

When working with SQL Server, temporary tables (#temp_table for local, ##global_temp_table for global) are invaluable for storing intermediate results, improving query performance, or simplifying complex logic. However, a common challenge arises when a script needs to be run multiple times: attempting to create a temporary table that already exists will result in an error. This article explores reliable techniques to check for a temporary table's existence and drop it if found, ensuring your scripts execute smoothly every time.

Why Conditional Dropping is Essential

Imagine a stored procedure or a batch script that creates a temporary table, populates it, and then uses it for further operations. If this script is executed a second time within the same session (for local temporary tables) or before the previous session has ended (for global temporary tables), the CREATE TABLE statement will fail with an error like "There is already an object named '#MyTempTable' in the database." To prevent such failures and ensure idempotency, it's crucial to implement a check-and-drop mechanism before attempting to create the temporary table.

flowchart TD
    A[Start Script Execution] --> B{Temporary Table Exists?}
    B -- Yes --> C[Drop Temporary Table]
    B -- No --> D[Create Temporary Table]
    C --> D
    D --> E[Populate and Use Table]
    E --> F[End Script]

Workflow for conditionally creating a temporary table.

Methods to Check for Temporary Table Existence

SQL Server provides several ways to determine if a temporary table already exists. The most common and recommended approaches involve querying system views or using the OBJECT_ID() function. The choice often depends on whether you're dealing with local or global temporary tables and your preference for conciseness versus explicit checks.

Using OBJECT_ID() for Local Temporary Tables

The OBJECT_ID() function is a simple and efficient way to check for the existence of any object in the current database, including temporary tables. For local temporary tables, you simply pass the table name prefixed with a hash (#). If the table exists, OBJECT_ID() returns its object ID; otherwise, it returns NULL.

IF OBJECT_ID('tempdb..#MyLocalTempTable') IS NOT NULL
BEGIN
    DROP TABLE #MyLocalTempTable;
END;

CREATE TABLE #MyLocalTempTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE()
);

INSERT INTO #MyLocalTempTable (Name) VALUES ('Item A'), ('Item B');
SELECT * FROM #MyLocalTempTable;

Checking and dropping a local temporary table using OBJECT_ID().

Using OBJECT_ID() for Global Temporary Tables

The approach for global temporary tables is very similar to local ones, but you use the double hash prefix (##). Global temporary tables are stored in tempdb and are accessible across sessions, so the OBJECT_ID() check will correctly identify them.

IF OBJECT_ID('tempdb..##MyGlobalTempTable') IS NOT NULL
BEGIN
    DROP TABLE ##MyGlobalTempTable;
END;

CREATE TABLE ##MyGlobalTempTable
(
    GlobalID INT IDENTITY(1,1) PRIMARY KEY,
    Description NVARCHAR(255),
    LastUpdated DATETIME DEFAULT GETDATE()
);

INSERT INTO ##MyGlobalTempTable (Description) VALUES ('Global Data 1'), ('Global Data 2');
SELECT * FROM ##MyGlobalTempTable;

Checking and dropping a global temporary table using OBJECT_ID().

Using sys.tables or INFORMATION_SCHEMA.TABLES

While OBJECT_ID() is generally preferred for its conciseness, you can also query system views like sys.tables (for SQL Server 2005 and later) or INFORMATION_SCHEMA.TABLES to check for temporary tables. These methods are more verbose but offer greater flexibility if you need to inspect other table properties.

-- Using sys.tables (recommended for SQL Server 2005+)
IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE name LIKE '#MyLocalTempTable%')
BEGIN
    DROP TABLE #MyLocalTempTable;
END;

CREATE TABLE #MyLocalTempTable (Col1 INT);

-- Using INFORMATION_SCHEMA.TABLES (less common for temp tables)
-- Note: INFORMATION_SCHEMA.TABLES does not reliably show local temp tables
-- It's generally better to use OBJECT_ID() or sys.tables for tempdb objects.
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '##MyGlobalTempTable%')
BEGIN
    DROP TABLE ##MyGlobalTempTable;
END;

CREATE TABLE ##MyGlobalTempTable (ColA VARCHAR(50));

Checking for temporary tables using sys.tables and INFORMATION_SCHEMA.TABLES.

Best Practices and Considerations

While the methods above are effective, keep these best practices in mind for robust temporary table management:

• Scope of Temporary Tables: Understand the difference between local (#) and global (##) temporary tables. Local tables are session-specific and automatically dropped; global tables are visible to all sessions and persist until all referencing sessions close.
• Naming Conventions: Use descriptive names for your temporary tables. For local temporary tables, the unique suffix added by SQL Server means you should always use LIKE with a wildcard when querying sys.tables.
• Error Handling: In more complex scenarios, consider wrapping your CREATE TABLE statements in TRY...CATCH blocks, although conditional dropping usually prevents the most common creation errors.
• Performance: For simple existence checks, OBJECT_ID() is generally the most performant option as it avoids querying system tables directly.