Credibility of TABLE_SCHEMA column in INFORMATION_SCHEMA objects

Question

Credibility of TABLE_SCHEMA column in INFORMATION_SCHEMA objects

Thom A

SSC Guru

Points: 99133
More actions
March 12, 2020 at 2:22 pm

#3733632

So, this is a problem that many of us have been aware of for some time. That, as per the documentation, TABLE_SCHEMA might be incorrect:
** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.
What I'm looking at here is specifically why. I actually asked about this point on the document's GitHub, however, got nothing that actually gives any information; just someone telling me "it's wrong" without evidence or reason.
To quote from my own post over there, you can use sys.sp_helptext to get the definition of INFORMATION_SCHEMA objects. For the schema name, the definition specifically states that that value of TABLE_SCHEMA is derived from SCHEMA_NAME(o.schema_id). o is an alias of sys.objects, which means that the INFORMATION_SCHEMA objects are in fact using the sys.objects catalog view.
This, at least to me and others I've discussed with, infers that the real problem is the function SCHEMA_NAME, and that as the INFORMATION_SCHEMA objects use that function, they aren't reliable.
Can anyone validate this assumption? If TABLE_SCHEMA isn't a problem, why (apparently) is the INFORMATION_SCHEMA objects when they make use of sys.objects as the documentation suggests.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

There is no difference really, both use the internal system tables, haven't found any difference between the output of the two.

😎

A simple way to visualize the difference would be running the below queries with showplan on

SELECT * FROM INFORMATION_SCHEMA.TABLES;

SELECT * FROM sys.objects;

Thom A SSC Guru Points: 99133 More actions · Answer 2

Eirikur Eiriksson wrote:

There is no difference really, both use the internal system tables, haven't found any difference between the output of the two.

I can't say I have ever found a scenario when the value is wrong either, and I even tried to make it wrong. Even something like the below doesn't make it return the wrong result:

CREATE TABLE dbo.MyTable (ID int);

WAITFOR DELAY '00:00:05';
/*
--While this is waiting, run the below in a different connection:
CREATE SCHEMA TestSchema;

GO
ALTER SCHEMA TestSchema TRANSFER dbo.MyTable;
*/

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Mytable';
GO

DROP TABLE TestSchema.MyTable;
DROP SCHEMA TestSchema;

I realise that the INFORMATION_SCHEMA objects are incomplete (as in certain information is not exposed, rather than objects are missing), but incomplete and wrong are 2 entirely different things.

Therefore not sure what the documentation is really getting at.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Thom A wrote:

Therefore not sure what the documentation is really getting at.

The INFORMATION_SCHEMA is of course a "compatibility" schema, hence the warning in the documentation 😉

😎

Thom A SSC Guru Points: 99133 More actions · Answer 4

Eirikur Eiriksson wrote:

The INFORMATION_SCHEMA is of course a "compatibility" schema, hence the warning in the documentation 😉
😎

But the warning is specifically against the TABLE_SCHEMA columns, nothing else. It specifically states that the value TABLE_SCHEMA could be wrong; thus the question is why when it uses sys.objects (and SCHEMA_NAME)to get the name of the schema. it seems silly to make a warning that actually isn't true, and even more odd to recommend usage of something that the view itself is using.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 5

well that was a rollercoaster.

I had not noticed that tidbit in the documentation, and was all "aw, crap!"

Then I kept reading, and went "maybe I should test..."

but you guys did, so now I'm just "meh"

back to the nosey grindstone...lol

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

I wonder if this is based a bit on old pre-2005 wives' tales that possibly used to be true and a bit on the 5 Monkeys Syndrome or maybe on an unspoken truth that no one actually knows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 7

Pedantic here!: It is also grammatically incorrect; it should be "an object" not "a object"

Thom A SSC Guru Points: 99133 More actions · Answer 8

Jeff Moden wrote:

I wonder if this is based a bit on old pre-2005 wives' tales that possibly used to be true and a bit on the 5 Monkeys Syndrome or maybe on an unspoken truth that no one actually knows.

I wouldn't be surprised if that is the case Jeff. I'm honestly half tempted to send a create a pull request on the documentation removing the warning, and see if anyone notices/cares. Heh.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

Aye. And I'd also like to see them fix the actual code for the ordinal. All they need to do is change one bloody word in the code and update the documentation to remove the warning in the Remarks section of the documentation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99133 More actions · Answer 10

Jeff Moden wrote:

Aye. And I'd also like to see them fix the actual code for the ordinal. All they need to do is change one bloody word in the code and update the documentation to remove the warning in the Remarks section of the documentation.

We all know that wouldn't happen, as it would be a "breaking" change. 😉

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

Thom A wrote:

Jeff Moden wrote:
Aye. And I'd also like to see them fix the actual code for the ordinal. All they need to do is change one bloody word in the code and update the documentation to remove the warning in the Remarks section of the documentation.
We all know that wouldn't happen, as it would be a "breaking" change. 😉

We've reached entropy where it breaks things to fix things that are broken.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)