0

In this doc page on the use of the SQL standard INFORMATION_SCHEMA views, on the columns about schema names, the following warning is repeated each time:

** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. INFORMATION_SCHEMA views only represent a subset of the metadata of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.

A casual perusal of this view on my database shows that all of the schemas appear to be reported as expected, so I'm wondering what exactly the problem is we're being warned about here. Under what circumstances can INFORMATION_SCHEMA prove unreliable in this regard?

0

2 Answers 2

1

The INFORMATION_SCHEMA is a deprecated schema that I believe Microsoft keeps around because it's a notion of ANSI SQL (SQL-92) compliance. As the warning you quoted mentions, the INFORMATION_SCHEMA may be missing meta-data and information on some of the objects in your database. But Microsoft created the sys schema as a more complete and covering schema of meta-data for the objects within a database. The sys schema is continually maintained by Microsoft as new versions of SQL Server are released.

This StackOverflow answer by Aaron Bertrand goes further into the details on what trouble you can run into when using the INFORMATION_SCHEMA due to its unreliability. One specific example from Aaron Bertrand's answer is the lack of support or deprecated representation of the following (as quoted by him):

Because the catalog views continue being developed as new features are added to SQL Server, while the info_schema views have not. As I mentioned in my comment, try to find information about filtered indexes in info_schema. Same goes for included columns, XML indexes, identity/computed columns, foreign keys against unique indexes - these are all either missing entirely or represented differently in the info_schema views.

0

I agree with the other comments. These views are there just because "they have to". A bit of history might shine some light on the subject:

Before 2005, we has the system tables. And the structure for those changes over time, as the product evolve. Not a good case for backwards compatibility. So in 7.0 (1998) MS introduced these views, which also was compatible with a proposal for exposing meta-data through view in the ISO SQL standard. They were, however, extremely limited, even by 1998 standard in how much they exposed.

Then came 2000.

Then came 2005, and MS re-designed the system table architecture (forced by partitioning invalidating the simplicity around sysindexes). While doing that, they stopped and think for a while and decided to give us a proper and rich interface to meta-data: Catalog views.

I don't have any such insight into MS at that level, but I can imagine that the development around the information_schema views pretty much halted when the catalog views were introduced.

Bottom line: Do I want to spend time on something which is essentially dead? No. :-)

I can agree that the wording in the documentation is vague and perhaps even misleading. One can decide to open a case with MS to correct this, but I doubt that you'll find somebody at MS that would spend time to dig into the specifics regarding what they mean by "reliable". The likely outcome would be a re-wording of that text.

I have a feeling that the fact that they mainly warn for the schema part is because many moons ago INFORMATION_SCHEMA.SCHEMATA had one row per database and not one row per schema. This was because before 2005, we didn't have schema (in the sense we do now). This was fixed in some version (2008?, 2008R2?, 2012?). My guess that this forgotten history is the background behind those warnings, and the warnings might be technically incorrect because the details got lost over time.

Not the answer you're looking for? Browse other questions tagged or ask your own question.