Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: OBJECT_NAME Takes Two Parameters

Database Programming: OBJECT_NAME Takes Two Parameters

  • Comments 1
  • Likes

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
use
msdb
go
-- 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)
go

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

-----------
-202

-------------------
sysdatabases

-------------------
sysdatabases

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!

-wp

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment