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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts