When it comes to querying data in SQL Server, we often find ourselves using alternative methods that we are comfortable with. However, there are simpler solutions that we may not be aware of or may not see the value in. In today’s blog post, we will discuss a method to retrieve the schema name from an object ID that is not commonly practiced.

Let’s take a look at the following code snippet:

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

There is nothing wrong with this code. It is a valid way to retrieve the schema name from an object ID. However, there is an alternative method that can achieve the same result with less typing. Instead of using the JOIN statement, we can use the OBJECT_SCHEMA_NAME function.

Here is an example of how we can rewrite the code:

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

Both of the above code snippets will give you the exact same result. If you remove the WHERE condition, it will provide information for all the tables in the database.

Now, the question arises: which method is better? The answer is subjective. It depends on personal preference and the specific requirements of the task at hand. In my case, I prefer using the second method as it requires less typing and is more concise.

What about you? Which method do you use to retrieve the schema name from an object ID, and why? Feel free to share your thoughts and preferences in the comments below.