#0315-SQL Server-Different ways to check for existence of an object (table/SP, etc)


I was recently reviewing the deployment scripts for a couple of products and I noticed that each team/DBA had their own style – and neither of them was incorrect. So, I sat about documenting each of those different styles for my own academic interest. I was able to document a total of 6 different ways in which various teams/DBAs and tools check for existence of a SQL Server object (table, stored procedure, etc).

These 6 different ways are shown below:

USE AdventureWorks2012;
GO

--1. The ISO compliant way
SELECT *
FROM INFORMATION_SCHEMA.TABLES AS ist
WHERE ist.TABLE_SCHEMA = 'HumanResources'
  AND ist.TABLE_NAME = 'Employee';
GO

--2. Using SQL Server Catalog Views
SELECT *
FROM sys.tables AS st
WHERE st.schema_id = SCHEMA_ID('HumanResources')
  AND st.name = 'Employee'
  AND st.is_ms_shipped = 0; --We are only looking for user objects!
GO

--3. Using SQL Server Catalog Views
SELECT *
FROM sys.objects AS so
WHERE so.type = 'U'
  AND so.schema_id = SCHEMA_ID('HumanResources')
  AND so.name = 'Employee'
  AND so.is_ms_shipped = 0; --We are only looking for user objects!
GO

--4. Using the OBJECT_ID function (The easy way)
--If the OBJECT_ID does not return a NULL value, the object exists
--For the object type value, refer http://technet.microsoft.com/en-us/library/ms190324.aspx
SELECT OBJECT_ID('HumanResources.Employee','U') AS ObjectId;
GO

--5. A Hybrid appraoch
SELECT *
FROM sys.objects AS so
WHERE so.object_id = OBJECT_ID('HumanResources.Employee','U')
  AND so.is_ms_shipped = 0; --We are only looking for user objects!
GO

--6. The SSMS way 
--   (if you have set your scripting options to check for object existence)
SELECT *
FROM sys.objects AS so
WHERE so.object_id = OBJECT_ID('HumanResources.Employee')
  AND so.type IN ('U');
GO

You can see here that we have a wide variety of methods that check for existence of an object – from ISO compliant ways to purely SQL Server specific ways.

My favourite method is method #4, using the function OBJECT_ID(). What one is your favourite?

Do note that OBJECT_ID will not work for objects which are not schema scoped, e.g. DDL triggers.

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

1 thought on “#0315-SQL Server-Different ways to check for existence of an object (table/SP, etc)

  1. www.google.com/accounts/o8/id10

    We use #4 FTW!

    Besides OBJECT_ID is way faster than accessing INFORMATION_SCHEMA views. The same “performance boost” we got from using IF COL_LENGTH(‘table’, ‘column’) IS NULL instead of INFORMATION_SCHEMA.COLUMNS or sys.columns

    Like

    Reply

Leave a Reply to www.google.com/accounts/o8/id10 Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.