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
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?
LikeLike
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’)
LikeLike
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…
LikeLike
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
LikeLike
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
LikeLike