Tuesday, February 3, 2009

Database Programming: OBJECT_NAME Takes Two Parameters


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
use msdb
-- 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!


Published Tuesday, February 03, 2009 7:45 PM by Ward Pond

Ward Pond's SQL Server blog : Database Programming: OBJECT_NAME Takes Two Parameters

No comments:

Blog Archive