SQL Server Check If Object Exists – 2 Methods

By /

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

Scroll to Top