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