Database Programming: OBJECT_NAME Takes Two Parameters

This post is a cousin of sorts to last December’s post regarding the OBJECT_ID function.  I learned this from Dirk Gubbels when he sent me a follow-up email to the seasonal T-SQL he passed along (the posting of which spawned a small flood).  This “trick” is actually a SQL Server 2005 enhancement, so it will also work in SQL Server 2008, but unfortunately not in SQL Server 2000.

Here’s a small T-SQL script which makes the point succinctly:

set nocount on
— note the feature we “discovered” in the previous post
select object_id(‘master.dbo.sysdatabases’)
— use the functionality of the previous discovery to build
  an environment-neutral demonstration of the new “discovery”
select object_name(object_id(‘master.dbo.sysdatabases’),db_id(‘master’))
— this instantiation of the new discovery should produce identical
  results to the above
select object_name(-202,6)

This script will produces the following output on the SQL Server 2005 instance on my laptop:




In SQL Server 2000, if you wanted to run the OBJECT_NAME() function outside the current database context, dynamic SQL was the only option (to either build a call to OBJECT_NAME() or the correct copy of the sysobjects table).  This new syntax allows the call to be built in-line, which offers an additional arrow in the quiver of T-SQL coders on the SQL Server 2005 and SQL Server 2008 platforms.

Thanks for the head-up, Dirk!


Comments (1)

  1. Anonymous says:

    ..but, once again, the dark underbelly of human nature has shown itself in my trackback pool.. Back in