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
GO
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)
GO

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)
GO

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 (https://blog.sqlauthority.com)

SQL Scripts, SQL System Table
Previous Post
SQLAuthority News – Download Whitepaper – Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services
Next Post
SQL SERVER – SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series – SQL Exam Prep Series 70-433

Related Posts

11 Comments. Leave new

  • Second method is more understandable

    Reply
  • agree with you… less typing..

    Reply
  • Dattatrey Sindol (Datta)
    May 15, 2012 12:58 pm

    Second method is definitely better as it requires less typing and keeps the script simple. However, at the end of the day, its a matter of choice :)

    Reply
  • 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’

    Reply
  • Roji P Thomas
    May 15, 2012 11:22 pm

    In the spirit of less typing :)

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

    Reply
  • Hi can I fetch data from a table if I have the OBJECT_ID of the table?

    Reply
  • 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

    Reply
  • It isn’t really. If you have two tables with the same name but in different schemas, the second one will give one answer where the first one will give you two rows.

    Reply
  • AlwaysLearning
    March 19, 2018 8:20 am

    Pinal, sorry, they do not produce the same results. The first query is more correct, the second one is deceptively wrong.

    Try creating tables with the same names (but different schemas) to see how they behave…

    CREATE SCHEMA TestSchema1 AUTHORIZATION dbo;
    GO
    CREATE SCHEMA TestSchema2 AUTHORIZATION dbo;
    GO
    CREATE TABLE TestSchema1.tables (
    table_id INT NOT NULL PRIMARY KEY
    );
    GO
    CREATE TABLE TestSchema2.tables (
    table_id INT NOT NULL PRIMARY KEY
    );
    GO
    SELECT * FROM SYS.OBJECTS WHERE NAME = ‘tables’
    — e.g.:
    — TestSchema1.tables => object_id 245575913
    — TestSchema2.tables => object_id 277576027
    GO

    — First method returns two rows…
    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(245575913)
    — e.g.:
    — SchemaName TableName schema_id OBJECT_ID
    — TestSchema1 tables 5 245575913
    — TestSchema2 tables 6 277576027
    GO

    — Pinal’s method returns two rows (but with incorrect SchemaName)…
    SELECT OBJECT_SCHEMA_NAME(245575913) AS SchemaName, t.name AS TableName,
    t.schema_id, t.OBJECT_ID
    FROM sys.tables t
    WHERE t.name = OBJECT_NAME(245575913)
    — e.g.:
    — SchemaName TableName schema_id OBJECT_ID
    — TestSchema1 tables 5 245575913
    — TestSchema1 tables 6 277576027
    GO

    If you’re passing in an OBJECT_ID and want to return the schema name and table name just for that object it would be more correct to use the following:

    SELECT OBJECT_SCHEMA_NAME(t.OBJECT_ID) AS SchemaName, t.name AS TableName,
    t.schema_id, t.OBJECT_ID
    FROM sys.tables t
    WHERE t.object_id = 245575913
    GO

    Reply
  • Only the 1st one would work on the linked server, if SYS is properly prefixed w/ ServerName.DBName…

    Reply

Leave a Reply