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');
This may sound stupid, but I would like to know if there is a way to verify if a list of tables exists before doing an action. If I have 12 tables to verify, do I have to repeat “If exists bla bla bla” 12 times?
I tried doing …
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N’employee_id’
and TABLE_NAME = N’employee_address’
and TABLE_NAME = N’employee_division’ )
But it not working. Any idea?
You can and the EXISTS clauses together:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N’employee_id’)
AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N’employee_address’)
AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N’employee_division’)
FYI, logically using AND doesn’t make sense in the same WHERE clause…I don’t know of anyone who’s first name is “Mary” AND “Joe” at the same time. It’s either one or the other, but it can’t be both, therefore it will always return FALSE; even if it’s “MaryJoe” 🙂
Sophia’s method works…
No its wrong
actual ans will be
FOR TABLE USE
IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N’Schemaname.Tablename’) and type in(N’U’))
and
for procedure and views
if OBJECT_ID(View/Procedure)IS NOT NULL
DROP View/Procedure
.
.
.
.
.
ETC
SP2-0734: unknown command beginning “IF NOT EXI…” – rest of line ignored.
drop table bkp
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol “DROP” when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with
<<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
INCORRECT