Database Programming: OBJECT_ID Takes Three-Part Identifiers
This is another one of those tricks that’s been available since the earth was cooling, but I just discovered it recently.
So, here’s a history lesson in the form of a small T-SQL script, with the moral contained in the title of this post:
set nocount on
use master
select object_id('sysobjects') as ObjectIdFromMaster
gouse tempdb
goselect object_id('master.dbo.sysobjects') as ObjectIdFromTempdb
select id as ObjectIdFromCatalogView
from master.dbo.sysobjects
where name = 'sysobjects'
go
All three SELECT statements return the same result, proving that the OBJECT_ID function will indeed properly process a three-part name:
ObjectIdFromMaster
------------------
-105ObjectIdFromTempdb
------------------
-105ObjectIdFromCatalogView
-----------------------
-105
This works all the way back to SQL Server 2000, which is the oldest platform I’ve got conveniently available.
Thanks to my colleague Venkata Raj Pochiraju for showing me this classic.
-wp