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:

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

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