validate if table exists while selecting and inserting SQL Server

Hello I have a dynamic query like

SET  @template = 'SELECT x AS X,... INTO tempor开发者_StackOverflow中文版alTable FROM' + @table_name 

Then I execute it

EXEC (@template)

• How do I validate if temporalTable already exists, if so, drop it?

Just use OBJECT_ID

IF OBJECT_ID('temporalTable') IS NOT NULL
   DROP TABLE temporalTable

No need to query any tables or do any aggregations.

Use information schema or sp_help function.

I would prefer information schema since it's SQL ANSI and you can port the code to other databases:

select count(1)
  from information_schema.tables 
 where table_name = 'temporalTable';

sys.tables is a SQLServer specific option similar to inforamtion schema that you can also explore.

IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_NAME='tablename') 
        SELECT 'tablename exists.' 
ELSE 
        SELECT 'tablename does not exist.'