Saturday, 21 January 2017

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
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
--------------------------------------------------------------------------------------------------------

1 comment:

  1. Thank you for any other informative website. The place else could I
    am getting that type of information written in such an ideal
    approach? I have a project that I am simply now working on, and I have been at
    the look out for such info.

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts