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

• OBJECT_ID function [Books On Line Link]
• Object Type enumeration based on sys.objects [Books On Line Link]

Until we meet next time,

Be courteous. Drive responsibly.