Stop Using INFORMATION_SCHEMA and SysObjects for Exists Checks

Code like this isn’t optimal:

IF EXISTS(SELECT * FROM sys.objects WHERE name = N'TableToDrop' AND type = 'U')DROP TABLE TableToDrop;GO

Neither is this:

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableToDrop')DROP TABLE TableToDrop;GO

Yet, I see both of those used all of the time – over and over again. Even though it’s 2015.  

It’s like devs, DBAs, product vendors, and developers all missed the memo that’s there’s a cleaner, simpler, and better way:

IF OBJECT_ID('dbo.TableToDrop','U') IS NOT NULLDROP TABLE dbo.TableToDrop;GO

Yup, OBJECT_ID() – with the addition of a SECONDARY argument (as of SQL Server 2005) that lets you specify the type of object you’re looking for – is you’re new best friend (if you’re still doing EXISTS checks the old fashioned way).

Act Now to Get Free Support for UDFs, Views, Sprocs, and More!

And, of course, it works with sprocs, UDFs, and anything else you can define via sys.objects’ type column will work as expected:

-- Scalar Function:IF OBJECT_ID('dbo.GetFormattedPrice','FN') IS NOT NULLDROP FUNCTION dbo.GetFormattedPrice;GO-- ViewIF OBJECT_ID('dbo.AllClientsReport','V') IS NOT NULLDROP VIEW dbo.AllClientsReport;GO-- etc

Using OBJECT_ID is simpler than firing off a sub-query, takes up less space, and does a better job of explaining your intentions. (At least until we get first-class support for CREATE or REPLACE syntax with a future version of SQL Server.)