[DOC] : INFORMATION_SCHEMA.TABLES gives improper warning about schema - by AaronBertrand

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
1
Sign in
to vote
ID 686118 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 8/29/2011 8:45:24 PM
Access Restriction Public

Description

In the following topic:

http://msdn.microsoft.com/en-us/library/ms186224.aspx

For the TABLE_SCHEMA column, this warning appears:

<snip>
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.
</snip>

This warning is not correct. If you look at the object_definition of INFORMATION_SCHEMA.TABLES, it points to the same schema_id column as sys.objects, so there is no situation where sys.objects would be correct but INFORMATION_SCHEMA would be incorrect.

Perhaps this is a lingering piece of information from a time when objects could be orphaned in a way that INFORMATION_SCHEMA views were not updated (e.g. my messing with system tables directly)?

Now, I realize that we do want to push people toward the catalog views, however it is for a different reason. We are not afraid that INFORMATION_SCHEMA views are incorrect, but rather that they are incomplete. For example, the INFORMATION_SCHEMA views contain no information about newer features such as included columns and filtered indexes.
Sign in to post a comment.
Posted by Maurice Pelchat on 8/5/2013 at 6:02 PM
You said you updated the topic and I still find that the column table_schema gives improper results. Is it or is it not giving the correct result?
Posted by Microsoft on 8/29/2012 at 3:42 PM
Thanks for your feedback. I updated the topic by adding "INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features" to the alert.
The alert now reads:
Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view. INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features.
Posted by Microsoft on 8/31/2011 at 4:51 PM
Hi Aaron,
That particular wording has been in the topic since 2005. But, I see your point. We'll revist the wording and try to make it more clear.

Regards,
Gail Erickson
SQL Server Documentation Team