How to Check whether If a Table already exists or not in SQL Server

SQL Server IF Condition to Check whether a Table already exists or not 
Use TAMATAM
GO
'Checking in Current Database(TAMATAM) and Schema(dbo) whether a Table already exists , the Dropping that Table.
IF OBJECT_ID(N'TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE TBL_Temp
End

IF OBJECT_ID(N'[dbo].TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE [dbo].TBL_Temp
End

--The Usage of 'N is optional here.
'Checking whether a ##Temp Table already exists or not , If exists then Drop that Table, Else re-create it.

IF OBJECT_ID('[tempdb].[dbo].[##StagingTable]', 'U') IS NOT NULL
Begin
       DROP TABLE [dbo].[##StagingTable]
       Print ('Temp Table already exists and it has been Dropped')
End
Else
Begin
       CREATE TABLE [dbo].[##StagingTable](
        [ Order_Id] [float] NULL,
        [ProdName] [nvarchar](255) NULL,
        [RegionName] [nvarchar](255) NULL,
        [FiscalQtr] [nvarchar](255) NULL,
        [NetSales] [float] NULL
                ) ON [PRIMARY]
       Print ('A Temp Table has been Created')
End
GO

-----------------------------------------------

'Checking in Current Database(TAMATAM) and Schema(dbo) whether a Table already exists or not Print the status.
IF OBJECT_ID('[dbo].[EMP_Test]','U') IS NOT NULL
PRINT 'Table Exists'
ELSE
PRINT 'Table Doesnot Exist'
GO

IF EXISTS (Select * FROM Sys.Objects Where Name='EMP_Test' and Type='U')
PRINT 'Table Exists'
ELSE
PRINT 'Table Doesnot Exist'

GO
-----------------------------------------------

Notes :
Type ='U' indicates the Object_Type is a Table
Type ='V' indicates the Object_Type is a View
Type ='P' indicates the Object_Type is a Procedure

-----------------------------------------------
'Checking in Specified Database(AdventureWorksDW2012) and Schema(MySCH) whether a Table already exists , if true then Dropping that Table.

IF OBJECT_ID(N'[AdventureWorksDW2012].[MySCH].TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE [AdventureWorksDW2012].[MySCH].TBL_Temp
End

IF OBJECT_ID(N'[TAMATAM].[MySCH].TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE [TAMATAM].[MySCH].TBL_Temp
End

'Checking whether a Table already exists , the Dropping that Table.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'MyTable')
BEGIN
 PRINT 'Table Already Exists and its Dropped'
 -- Drop Table MyTable
END
ELSE
'If a Table not exists , then creating the Table(I created using Join here )
BEGIN
SELECT * INTO MyTable from (
                   SELECT  P.Prod_Id,P.Prod_Name,P.Sales_Region,Sales_Period,
                                     C.Cust_Id,C.Cust_Name, C.Cust_Loc ation 
                    FROM Products P 
Left JOIN
                Customers C
  ON P.Cust_Id=C.Cust_Id 
   WHERE C.Cust_Id IS NULL ) S1 
--Select * From MyTable
PRINT 'Table Successfully Created'
END
GO
-----------------------------------------------
Notes :
We can also check the Table existence in SQL Server using the below Methods.
Using sys.Objects Catalog View :
We can use the Sys.Objects catalog view to check the existence of the Table as shown below:
IF EXISTS(SELECT 1 FROM sys.Objects WHERE  Object_Id = OBJECT_ID(N'dbo.MyTable') AND Type = N'U')
BEGIN
   PRINT 'Table Exists'
END

Using sys.Tables Catalog View:
We can use the Sys.Tables catalog view to check the existence of the Table as shown below:
 IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'MyTable' AND Type = N'U')
 BEGIN
      PRINT 'Table Exists'
 END

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------