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

'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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts