Copy, paste and run this code in your SQL Server Management Studio to build the database and table we are using in this exercise:
create database sandbox;
use sandbox;
CREATE TABLE employee_id (
emp_nm varchar(30) not null,
emp_id varchar(8),
b_emp_id varchar(8),
PRIMARY KEY(emp_nm) );
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; Select * from INFORMATION_SCHEMA.TABLES;
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 = 'employee_id') BEGIN PRINT 'Yes' END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '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 = 'employee_id') BEGIN drop table employee_id END ELSE BEGIN print 'Table does not exist' End 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');