Practical SQL Server

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 NULL
	DROP 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 NULL
	DROP FUNCTION dbo.GetFormattedPrice;
GO

-- View
IF OBJECT_ID('dbo.AllClientsReport','V') IS NOT NULL
	DROP 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.)

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish