OBJECT_ID()’s second parameter

I received a supplier-supplied maintenance / check / troubleshooting script that did a whole heap of looking-to see-if-a-default-value-exists-and-if-it-doesn’t-then=create-it type stuff.

As you would expect, it’s a load of

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'<<DefaultName>>') AND type = 'D')

BEGIN

    ALTER TABLE ... ADD CONSTRAINT...

END

They could have saved themselves a bit of typing (or, let’s be honest, copying-and-pasting) by replacing all those IF NOT EXISTS with

IF OBJECT_ID(N'<<DefaultName>>', N'D') IS NULL

BEGIN

    ALTER TABLE ... ADD CONSTRAINT...

END

Yes. OBJECT_ID can take a second parameter, which is the type of object to look for.

It might be worth noting that, although the type value in sys.objects is of type char(2), the type parameter in OBJECT_ID is implicitly converted to nvarchar if it’s not declared as such. Is this some far-sighted future-proofing? Check the documentation for sys.objects to see the possible values for the type field.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged . Bookmark the permalink.

Leave a Reply

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.