#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.

Advertisements

One 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

Let me know what you think about this post by leaving your feedback here!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s