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
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