To check if a table exists in SQL Server, you can use the INFORMATION_SCHEMA.TABLES table.
Running the following code, produces the results below:
USE SANDBOX GO; Select * from INFORMATION_SCHEMA.TABLES GO;
You can use this table with an IF THEN clause do determine how your query responds whether or not a table exists.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'employee_id') BEGIN PRINT 'Yes' END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'employee_ids') BEGIN PRINT 'Yes' END ELSE BEGIN PRINT 'No' End
One of the more common uses I find for this when I need to create a table in a script. I want to make sure a table with same name doesn’t already exist, or my query will fail. So I write a query like the one below.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'employee_id') BEGIN drop table employee_id END ELSE BEGIN CREATE TABLE employee_id ( emp_nm nvarchar(30) not null, emp_id nvarchar(8), b_emp_id nvarchar(8) PRIMARY KEY(emp_nm) ); INSERT INTO employee_id (emp_nm, emp_id) VALUES ('Bob', 'A1234567'), ('Lisa', 'A1234568') INSERT INTO employee_id (emp_nm, b_emp_id) VALUES ('Priyanka', 'B1234567'); End