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.

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