If you work with SQL Server, you may have encountered situations where you need to check if an object exists in your database. This could be a table, view, stored procedure, or any other type of object. In this article, we’ll explore two methods for checking if an object exists in SQL Server.
Method 1: Using sys.objects
One common way to check if an object exists in SQL Server is to use the sys.objects table. You can write a query like this:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N'[dbo].[my_table]') AND type in (N'U')) -- do something
To be honest, I used to do it this way too until I found out that there is a simpler, and faster way. Simply check to see if the OBJECT_ID function returns null or not.
Method 2: Use the OBJECT_ID
IF OBJECT_ID (N'[dbo].[my_table]',N'U') IS NOT NULL. -- returns NOT NULL if table exsts -- do something
This is a much more efficient way because there is no sub-query involved.
NOTE: Both methods check the current database (each database has its own sys.objects table) for a table called my_table. User defined tables are type ‘U’, the second argument to OBJECT_ID
Here’s a handy list of the object types (U, V and P are the ones you’ll use most often):
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
U = Table (user-defined)
V = View
To check another database besides the current one, include the fully qualified database.owner.object name
Eg [production].[dbo].[my_table] looks in the production database for the table my_table
A 30+ year database veteran. I enjoy sharing my stories and knowledge with others.