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]..[##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 , the Dropping that Table.
IF OBJECT_ID(N'[AdventureWorksDW2012].[MySCH].TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE [AdventureWorksDW2012].[dbo].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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts