INFORMATION_SCHEMA the un-sung system views
13April 29, 2013 by Kenneth Fisher
I spend a reasonable amount of time looking through various forums, answering some questions and reading the answers to others. One of the things I’ve noticed is that while I frequently see system views referenced I only very rarely see the INFORMATION_SCHEMA views mentioned. This seems rather strange to me since I think most (although by no means all) of the people who work with system views know about the INFORMATION_SCHEMA ones.
For those who haven’t seen them before I’m going to show a few examples, comparing each INFORMATION_SCHEMA (owned by the INFORMATION_SCHEMA schema) view to the more common sys (owned by the sys schema) system view I see. Note: I’m only showing the base sys system view. In most (if not all) cases retrieving the same information will require joining several sys system views and/or functions together.
INFORMATION_SCHEMA.TABLES vs sys.tables
INFORMATION_SCHEMA.COLUMNS vs sys.columns
INFORMATION_SCHEMA.VIEWS vs sys.views
INFORMATION_SCHEMA.VIEW_TABLE_USAGE vs sys.dependencies
In each case you will notice that the biggest difference between the two is that the INFORMATION_SCHEMA views seem to have been designed for ease of use, or “reporting” type output. Unlike the sys system views the INFORMATION_SCHEMA views display names and descriptive values, where appropriate, rather than numeric ids. So for example INFORMATION_SCHEMA.COLUMNS has the column “DATA_TYPE” with an English description of the data type for the column as compared to sys.columns which has the “user_type_id” column that has to be translated using the sys.column_type_usages system view. INFORMATION_SCHEMA.COLUMNS also has the table name and the schema name for the parent table rather than the object_id found in sys.columns. When using the object_id it is necessary to do a lookup. Using either the system functions OBJECT_NAME and PROPERTYEX or sys.objects and sys.schemas to get the table name and/or schema name.
So on the up side the INFORMATION_SCHEMA views are much easier to read, however, on the down side they have much less information. For example the INFORMATION_SCHEMA.TABLES view has only 4 columns. Columns to make up the three part name (database.schema.tablename) of the table and the TABLE_TYPE (BASE TABLE, VIEW etc). In comparison the sys.tables view in SQL 2008 R2 has 27 columns that include such information as created and modified date, is_ms_shipped (did it come from Microsoft), is_replicated and lock escalation. So you can see that the sys views have quite a bit more information, even if it is a bit harder to use.
As a side note, one other difference between the INFORMATION_SCHEMA system views and the sys system views is the case of the names. For the sys views all of the column and table names are lower case (that I’ve encountered) and for INFORMATION_SCHEMA they are upper case (again that I’ve encountered). Not really important, unless you happen to be working on a case sensitive system. And while case sensitive systems are not the most common thing in the world they are not all that unusual.
So when do I use INFORMAITION_SCHEMA?
When I need a list of tables and their schema but no other information. Usually when I’m going to create some type of dynamic SQL.
SELECT * FROM INFORMATION_SCHEMA.TABLES
When I need to get a list of columns from a specific table. Again usually when I’m going to create some type of dynamic SQL from them. Or in the case of this example when I need to find all columns with a specific pattern from a specific table.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA' AND COLUMN_NAME LIKE '%COLPAT%'
And frequently I will suggest the INFORMATION_SCHEMA views to developers as easier to understand (and requiring less explanation). No offense to developers reading this, since to be quite honest, I would love the Objects for Dummies version of .net.
As of SQL 2008 R2 there are 20 different INFORMATION_SCHEMA views. I personally only use a handful but it is certainly worth reviewing the full list to see what could be useful to you.
SQL Studies 
The INFORMATION_SCHEMA views are not reliably accurate in SQL Server. That’s probably why you don’t see them being used that much 🙂 .
In what way are the inaccurate? The INFORMATION_SCHEMA.TABLES view for example is just a join on sys.objects and sys.schemas, so I’m not sure how it could be inaccurate?
I guess then you haven’t seen the disclaimers about I_S views in BOL: “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.”
Or the research by several people into its inaccuracies. The research indicates that the message is misleading, but not the overall advice: just don’t use I_S views in SQL Server.
I have seen that particular disclaimer. However if you look at the connect entry that Aaron Bertrand put in you will note that the text is rather misleading. It really means that the information has not always been updated to include new features. This doesn’t apply for fairly basic uses, such as getting a list of tables etc. I’ve read Aaron’s reasons for not liking the I_S views and I agree with some of them although personally I still use them for simple tasks. Can you point me to someone else who has posted specific problems with them (other than not including a fair amount of information)?
You can do Google searches yourself for that. You seem determined to continue using I_S views regardless, so I won’t bother you further.
I’ve actually done Google searches and only been able to find Aaron’s post. I certainly don’t mind having a discussion but honestly for the purposes I use them for (admittedly simple) I just don’t see a problem with them. If you look at the underlying code you will see there is no possible way for them to be “wrong” about the data that they show. I’d love to see proof otherwise but thus far I haven’t been able to find any. Feel free to post an example if you like.
As in my initial post, I was simply trying to answer your original (implied) q: “I only very rarely see the INFORMATION_SCHEMA views mentioned. This seems rather strange to me”. It’s not “strange” when one realizes how limited they are, and the disclaimers associated with them.
It’s simpler to just *always* use sys.objects. I don’t want to try to explain to developers or junior DBAs that “simple” queries can use i_s but “non-simple” queries must use sys. There’s no clear definition of “simple” … until/unless you run into a limitation in the i_s view. Every time you do, then you have to rewrite the code to use sys instead. That’s just silly, wasted effort to me when sys works for *all* code.
I see your point, and it is certainly a good one. I’m just not sure I agree with it completely. One of the ways that developers and junior DBAs become Sr Developers and Sr DBAs is by learning multiple ways to do multiple things, and learning when it is appropriate to do what. In this particular case if I want to tell a developer how to get a list of tables I would rather tell him to use I_S.TABLES than to use sys.tables and join it to sys.schemas, he is far more likely to get the data he needs and not forget to look at schemas at all. All that being said, again, you do have a good point.
I’d rather teach them the RIGHT way to do it the FIRST time. Btw, fyi, i_s.tables and sys.tables are not equivalent.
Any differences in locking between the two solutions? INFORMATION_SCHEMA locking on DML-heavy systems is brought up often.
I can’t see how it would be different. INFORMATION_SCHEMA.tables for example uses a join between sys.objects and sys.schemas to pull the information and INFORMATION_SCHEMA.views uses a query from sys.views. Try running sp_helptext on them and you can see the queries behind them if you are interested in any in particular.
Good point, thanks.
Reblogged this on Mavin world and commented:
Perfect one shot for the difference between INFORMATION_SCHEMA and the SYSTEM VIEWS