Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQl Server : Check table existence

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 555
    Comment on it

    A common problem encountered during SQL server development is to check if a particular table exists or not. There are different approaches to solve this problem and in this blog we will list out these approaches. For illustration purpose we will be using a table named Employees.

    Let us first create the Employees table:

    USE SampelDB
    GO
    CREATE TABLE dbo.Employees
    (EmpId INT, EmpName NVARCHAR(100))

     

    1) By using INFORMATION_SCHEMA.TABLES

      IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Employees')
       BEGIN
         PRINT 'Table Allready Exists'
       END

    The above query will check for the existence of the Employees table throughout all the schemas in the current database.

     

    2) By using OBJECT_ID() function

      IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL
       BEGIN
          PRINT 'Table Allready Exists'
       END

    In the above approach ,we can also  specify database Name in case we want to check the existence of the table in a specific database.


    3) By using sys.Objects Catalog View

     

    
       IF EXISTS(SELECT 1 FROM sys.Objects WHERE  Object_id = OBJECT_ID(N'dbo.Employees') AND Type = N'U')
       BEGIN
         PRINT 'Table Allready Exists'
       END

    Above approach uses the Sys.Objects catalog view to check the existence of the Table.

     

    4) By using sys.Tables Catalog

    
       IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'Employees' AND Type = N'U')
       BEGIN
          PRINT 'Table Allready Exists'
       END

    Above approach uses the Sys.Tables catalog view to check the existence of the Table.


    Hope the above article helps you in implementing checks for table existence while doing SQL server development.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: