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.
- OBJECT_ID function [Books On Line Link]
- Object Type enumeration based on sys.objects [Books On Line Link]
Until we meet next time,
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