SQL SERVER – Get Schema Name from Object ID using OBJECT_SCHEMA_NAME

Sometime a simple solution have even simpler solutions but we often do not practice it as we do not see value in it or find it useful. Well, today’s blog post is also about something which I have seen not practiced much in codes. We are so much comfortable with alternative usage that we do not feel like switching how we query the data.

I was going over forums and I noticed that at one place user has used following code to get Schema Name from ObjectID.

USE AdventureWorks2012
SELECT s.name AS SchemaName, t.name AS TableName,
s.schema_id, t.OBJECT_ID
FROM sys.Tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = OBJECT_NAME(46623209)

Before I continue let me say I do not see anything wrong with this script. It is just fine and one of the way to get SchemaName from Object ID.

However, I have been using function OBJECT_SCHEMA_NAME to get the schema name. If I have to write the same code from the beginning I would have written the same code as following.

SELECT OBJECT_SCHEMA_NAME(46623209) AS SchemaName, t.name AS TableName,
t.schema_id, t.OBJECT_ID
FROM sys.tables t
WHERE t.name = OBJECT_NAME(46623209)

Now, both of the above code give you exact same result. If you remove the WHERE condition it will give you information of all the tables of the database.

Now the question is which one is better – honestly – it is not about one is better than other. Use the one which you prefer to use. I prefer to use second one as it requires less typing. Let me ask you the same question to you – which method to get schema name do yo use? and Why?

Reference: Pinal Dave (http://blog.sqlauthority.com)

10 thoughts on “SQL SERVER – Get Schema Name from Object ID using OBJECT_SCHEMA_NAME

  1. I have used similar query in application as Follows, and I felt it is more easier to maintain.

    select s.name as SchemaName,t.name as TableName
    from sys.tables T, sys.schemas S
    where S.schema_id=T.schema_id
    and T.name=’Customers’


  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Building Three-Part Name from OBJECT_ID – Database Name, Schema Name, TableName | Journey to SQL Authority with Pinal Dave

  4. If you have the same table name under different schemes this script is bad.

    WHERE t.name = OBJECT_NAME(46623209)

    It should be this or you will get more than one result.

    WHERE t.OBJECT_ID = 46623209


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s