SQL Check if table exists

SQL Check if table exists in a database. creating a new table

SQL Check if table exists.Before creating a new table or before dropping a table you need to check if table exists in the database.
To check if table exists in a database you need to use a Select statement on the information schema TABLES or you can use the metadata function OBJECT_ID().
The INFORMATION_SCHEMA.TABLES returns one row for each table in the current database.
The OBJECT_ID() function returns the database object id number if the object exists in the database.

Example

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Certifications';

Result:

Table_Catalog	Table_Schema	Table_Name	Table_Type
model	dbo	Certifications	BASE TABLE

Check if table exists

IF (EXISTS (SELECT *
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Certifications'))
   BEGIN
      PRINT 'Database Table Exists'
   END;
ELSE
   BEGIN
      PRINT 'No Table in database'
   END;

Result:

Database Table Exists

Using OBJECT_ID() function

report this ad

IF OBJECT_ID('model.dbo.Certifications') IS NOT NULL
   BEGIN
      PRINT 'Database Table Exists'
   END;
ELSE
   BEGIN
      PRINT 'No Table in database'
   END;

Result:

Database Table Exists

See More: Add a column with a default value to an existing table in SQL

UPDATE from a SELECT SQL Server