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.