Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using the OBJECT_SCHEMA_NAME function to return the SQL Server schema name


By:   |   Read Comments (3)   |   Related Tips: More > Functions - System

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

Here is a quick tip that I did not see covered here at www.mssqltips.com and found quite useful when building ad-hoc Transact-SQL code.  You see, I tend to do a great deal of meta-data (or is it metadata) mining from the system compatibility views and system catalog views in order to build dynamic ad-hoc T/SQL code to use for various administrative processes. 

Since SQL Server 2005 was released though we finally see the SQL Server Professionals embracing the structures associated with schemas that Oracle Professionals had been using for years.  Sure, the schemas (aka owners) construct has been around in releases prior to SQL Server 2005, but most SQL Developers and DBAs avoided anything other than the default "dbo" schema like it was that weird cousin you would see at the family picnic each summer that would be chewing the bark off the oak trees and talking to the squirrels. 

SQL Server 2005, however, brought schemas to the mainstream, and the more-organized, and security-conscious SQL Professionals started incorporating schema reliance into their databases.  This, therefore, made it necessary to incorporate the coding for such use into the dynamic T/SQL I was developing.  There are ways of deriving this information from JOINs between catalog/compatibility views, but there is something even better: a function that returns this information without need for all that nasty code.

Solution

In this tip I'll use a simple query against the sys.dm_db_index_usage_stats Dynamic Management View (DMV).  The results of this view denote the usage of all indexes (usage is broken down into system versus user seeks, scans, lookups, and updates) on the SQL Server instance.  The first version of this query ignores schema all-together.  We will examine why this is a practice I don't recommend after we examine the output.

SELECT DB_NAME(SDDIUS.[database_id]) AS [db name], 
   OBJECT_NAME(SDDIUS.[object_id]) AS [Table/View], 
   SDDIUS.[index_id], 
   SDDIUS.[user_seeks] + SDDIUS.[user_scans] + SDDIUS.[user_lookups] AS [user reads], 
   SDDIUS.[user_updates] AS [user writes] 
FROM sys.[dm_db_index_usage_stats] SDDIUS 
WHERE DB_NAME(SDDIUS.[database_id]) = 'Northwind' 
ORDER BY OBJECT_NAME(SDDIUS.[object_id]), SDDIUS.[index_id] 

northwind

The query returns 22 rows.  These rows represent all activity since the last time the SQL Server service was restarted.  (This information only resides in cache.  See previous tips at the end of this article for steps on how to persist this data for index optimization practices.)  Those of you familiar with the sample Northwind database may already see what I am about to point out: there is a mix of system and user object indexes in the result set.  The objects starting with "sys..." are system-created tables or views.  I would prefer to exclude these from the results. 

There is also another matter - specifically concerning the user-created objects' indexes.  Do you see any "duplicates"?  Look closely.  Rows 2 and 3 both reference the Customers table's index_id of 1.  However, the combination of object_id and index_id is unique in this DMV.  So how could this be?  Simple - the tables are in different schemas.  Let's look at what we can see once we include schema information in the result set.

The following query uses multiple joins (via the sys.sysobjects and sys.sysusers System Compatibility Views) to return schema information for each object in the result set.  I've also proceeded to limit the results to only objects not owned by the sys schema.

SELECT DB_NAME(SDDIUS.[database_id]) AS [db name], 
   SSU.[name] AS [schema], 
   OBJECT_NAME(SDDIUS.[object_id]) AS [Table/View], 
   SDDIUS.[index_id], 
   SDDIUS.[user_seeks] + SDDIUS.[user_scans] + SDDIUS.[user_lookups] AS [user reads], 
   SDDIUS.[user_updates] AS [user writes] 
FROM sys.[dm_db_index_usage_stats] SDDIUS 
   INNER JOIN [sys].[sysobjects] SSO  
       ON SDDIUS.[object_id] = SSO.id 
   INNER JOIN sys.[sysusers] SSU  
       ON SSO.[uid] = SSU.[uid] 
WHERE DB_NAME(SDDIUS.[database_id]) = 'Northwind' 
   AND SSU.[name] <> 'sys' 
ORDER BY OBJECT_NAME(SDDIUS.[object_id]), SDDIUS.[index_id]

db name

Now you can see what was only inferred via familiarity with the design of the database and knowledge of the specific DMV, that there are actually two objects in the result set that are named identically, but owned by different schemas.  You may balk at this design strategy, but it is quite common and is considered a best practice when dealing with similar designs that could, in fact, be touched by different logical user groups in an organization.

Now that we see reasons for displaying schema information in your result sets when dealing with SQL Server metadata let's take that final step and look at the OBJECT_SCHEMA_NAME() function.  The previous example required you to understand the relationships between multiple system objects in order to return schema information with the result set.  OBJECT_SCHEMA_NAME() renders that knowledge requirement moot, and simplifies the query in the process.

SELECT DB_NAME(SDDIUS.[database_id]) AS [db name], 
   OBJECT_NAME(SDDIUS.[object_id]) AS [Table/View], 
   OBJECT_SCHEMA_NAME(SDDIUS.[object_id], SDDIUS.[database_id]) AS [schema], 
   SDDIUS.[index_id], 
   SDDIUS.[user_seeks] + SDDIUS.[user_scans] + SDDIUS.[user_lookups] AS [user reads], 
   SDDIUS.[user_updates] AS [user writes] 
FROM sys.[dm_db_index_usage_stats] SDDIUS 
WHERE DB_NAME(SDDIUS.[database_id]) = 'Northwind' 
   AND OBJECT_SCHEMA_NAME(SDDIUS.[object_id], SDDIUS.[database_id]) <> 'sys' 
ORDER BY OBJECT_NAME(SDDIUS.[object_id]), SDDIUS.[index_id]

northwind categories

The results are identical, but we eliminate the additional (and truly unnecessary) joins to sys.sysobjects and sys.syssusers.  We also make the code more readable.  The OBJECT_SCHEMA_NAME() function accepts two parameters:  an object_id and an optional database_id.  As you can see from my code, I specified the database_id.  Therefore this query would execute properly no matter which database's context I execute it under.  If I was always going to run this query from within the Northwind database I could exclude the database_id parameter from the function call as the function will automatically assume the database_id parameter is for the database currently in context if left unspecified.

I strongly advocate use of the OBJECT_SCHEMA_NAME() function when attempting to return schema/owner information for objects.  I also require presentation of schema/owner information whenever identifying objects in the databases in our organization.  Imagine the difficulties that could be encountered if an index is dropped or created on the incorrect table simply because the correct table was not identified completely via a query similar to what I presented here, but only in a full production optimization review.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 24, 2010 - 7:36:04 AM - Ozzie Back To Top

 Ah - because I switched the where clause to be

DB_NAME(SDDIUS.[database_id]) != 'MSDB' AND DB_NAME(SDDIUS.[database_id]) != 'TempDB' AND DB_NAME(SDDIUS.[database_id]) != 'Master' AND

Case closed.

Doug


Thursday, June 24, 2010 - 7:31:02 AM - Ozzie Back To Top

 So what does it indicate when you have rows where the Table/View are NULL - no index? Is there a way to pull out the table that did not havean index?

 

Wondering,

Doug


Thursday, June 04, 2009 - 11:50:14 AM - jerryhung Back To Top

 Keep in mind thatOBJECT_SCHEMA_NAME is ONLY in SQL 2005 SP2 or later, or SQL 2008 RTM

 Otherwise you will get the error "'OBJECT_SCHEMA_NAME' is not a recognized built-in function name."


Learn more about SQL Server tools