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)
11 Comments. Leave new
Second method is more understandable
agree with you… less typing..
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 :)
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’
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)
Hi can I fetch data from a table if I have the OBJECT_ID of the table?
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
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.
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
Only the 1st one would work on the linked server, if SYS is properly prefixed w/ ServerName.DBName…