OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

SQL Server 2005 SP2 has an important enhancement to OBJECT_NAME metadata function and a new OBJECT_SCHEMA_NAME metadata function. I will first describe the old functionality to give some context and demonstrate how the new features help a lot.

Please note that the examples uses DMVs that looks at metadata in each database or plan cache so performance of the queries depends on your environment, number of objects in the database, auto open/auto close setting of database, size of buffer pool or plan cache etc. So exercise caution if you run the query on a production database and it might be prudent just to restrict it to a specific database to see the results.

The OBJECT_NAME function is typically used to get the name of an object by specifying the object identifier (object_id value). For example, the query below returns the usage stats of indexes in the AdventureWorks database:

select OBJECT_NAME(i.object_id) as object_name, *
from sys.dm_db_index_usage_stats as i
where i.database_id = db_id('AdventureWorks');

Now, the above query will return the correct object name only if the query was run in the AdventureWorks database. This is because the metadata function OBJECT_NAME is database specific and uses information from the catalog tables in the current database. If you run the query in a different database (say master) then the names will either be wrong (because object_id may match a different object in master database) or NULL. So in order to get the correct object name, the query has to be executed in the context of the database you are interested (AdventureWorks in our example).

This DMV also returns information from multiple databases on the server. Let us now consider a scenario where you want to write a single query that returns say the top 5 entries from each database based on the number of user seeks that were performed on the index. The query will look like below:

select *
from (
 select *, DENSE_RANK() OVER(PARTITION by database_id ORDER BY user_seeks DESC) as rnk
 from sys.dm_db_index_usage_stats
) as i
where i.rnk <= 5;

To modify the query to return the user friendly object names instead of the identifiers, you will have to use dynamic SQL in older versions of SQL Server. This is because the OBJECT_NAME function always worked within the context of the current database. SQL Server 2005 SP2 removes this restriction and we now have an additional optional parameter that specifies the database id. This allows us to write a single query that can retrieve the OBJECT_NAME given the object identifier and database identifier of object in any database in a server. The modified query that lists the entries along with the object names is shown below:

select OBJECT_NAME(i.object_id, i.database_id) as objname, *
from (
 select *, DENSE_RANK() OVER(PARTITION by database_id ORDER BY user_seeks DESC) as rnk
 from sys.dm_db_index_usage_stats
) as i
where i.rnk <= 5;

Can you see how simple it is now to write queries that involve looking at object metadata in multiple databases? The enhancement to OBJECT_NAME now allows you to write complex queries that look at metadata in different databases without using dynamic SQL.

Lastly to round off the post, SQL Server 2005 has user schema separation and supports the ANSI SQL model of schemas. So it is always better to return object names along with their schema names. The OBJECT_SCHEMA_NAME metadata function can be used to return the schema name of a schema-scoped object like table or view by specifying the object identifier and optionally database identifier. The modified query below shows how to list the schema name also:

select OBJECT_SCHEMA_NAME(i.object_id, i.database_id) as schname
, OBJECT_NAME(i.object_id, i.database_id) as objname
, *
from (
 select *, DENSE_RANK() OVER(PARTITION by database_id ORDER BY user_seeks DESC) as rnk
 from sys.dm_db_index_usage_stats
) as i
where i.rnk <= 5;

The final query that shows the full object names along with the database in quoted format is shown below. The query lists the top 5 indexes in each database that had the most number of user_seeks performed.

select QUOTENAME(DB_NAME(i.database_id), '"')
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id, i.database_id), '"')
+ N'.'
+ QUOTENAME(OBJECT_NAME(i.object_id, i.database_id), '"') as full_obj_name, *
from (
 select *, DENSE_RANK() OVER(PARTITION by database_id ORDER BY user_seeks DESC) as rnk
 from sys.dm_db_index_usage_stats
) as i
where i.rnk <= 5
order by i.database_id, i.rnk;

Below are some more examples on how to use the new functions.

-- Returns top 5 fragmented indexes in each database:

select QUOTENAME(DB_NAME(i.database_id), '"')
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id, i.database_id), '"')
+ N'.'
+ QUOTENAME(OBJECT_NAME(i.object_id, i.database_id), '"') as full_obj_name
, *
from (
 select *, DENSE_RANK() OVER(PARTITION by database_id ORDER BY avg_fragmentation_in_percent DESC) as rnk
 from sys.dm_db_index_physical_stats(default, default, default, default, default)
 where avg_fragmentation_in_percent > 0
) as i
where i.rnk <= 5
order by i.database_id, i.rnk;

-- Returns top 10 statements (in sql modules) per database that took the most elapsed time:

select coalesce(QUOTENAME(DB_NAME(t.dbid), '"'), '') /* NULL means resource db*/
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid), '"')
+ N'.'
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid), '"') as full_obj_name
, SUBSTRING(t.text, (t.statement_start_offset/2)+1,
((CASE t.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE t.statement_end_offset
END - t.statement_start_offset)/2) + 1) AS statement_text
, t.*
from (
 select *, DENSE_RANK() OVER(PARTITION BY t.dbid ORDER BY qs.total_elapsed_time) as rnk
 from sys.dm_exec_query_stats as qs
 cross apply sys.dm_exec_sql_text(qs.sql_handle) as t
 where t.objectid is not null
) as t
where t.rnk <= 10
order by t.dbid, t.rnk;

Hope you find these enhancements in SQL Server 2005 SP2 useful and productive. Feel free to comment if you need more examples or more use cases.

--

Umachandar