SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

INFORMATION_SCHEMA the un-sung system views

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.





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.


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.


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.


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.

Filed under: Dynamic SQL, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, INFORMATION_SCHEMA, language sql, microsoft sql server, sql statements, system functions, T-SQL


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...