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
-- use the functionality of the previous discovery to build
-- an environment-neutral demonstration of the new "discovery"
-- this instantiation of the new discovery should produce identical
-- results to the above
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!