The Wiert Corner – irregular stream of stuff

Few people know about the INFORMATION_SCHEMA views that have been there since SQL-92.

Two funny things about that standard:

• Until the 1990’s, Digital Equipment Corporation was still heavily involved in SQL.
  Back then Jim Melton from Digital Equipment Corporation (now at W3C, before that at Oracle and Sybase), was the editor of the SQL-92 standard.
  Another Jim worked at DEC until 1981 with great influences on other relational databases too.
• Of the four levels in the SQ-92, no database vendors have been certified higher than the “Entry Level”.

A few reasons I can imagine not many people know about the INFORMATION_SCHEMA:

• Not all relational database servers implement them, and of the ones that do implement them, not all versions implement all views.
  That’s what this article is about; currently it lists only SQL Server 2000 and 2008 R2 (tried both) and PostgreSQL (verified docs), but I will update it as soon as I have run the script on other versions of SQL server and database vendors.
• It is hard to find the official ISO standards in a public way, and ISO itself sometimes puts things online, and at other times wants money for it
  

  I remember the Y2K preparation era where the ISO-8601 standard was freely available at http://www.iso.ch/markete/8601.pdf, soon after the Year 2000, the PDF got locked behind a payment engine.
  ISO suffers from heavy link rot too, for instance the ISO 3166 country codes used to be at http://www.iso.org/iso/prods-services/iso3166ma, but are now at http://www.iso.org/iso/home/standards/country_codes.htm. What about HTTP 303 or 302 redirect here guys?

Since SQL-92, the INFORMATION_SCHEMA (and its twin DEFINITION_SCHEMA) have been extended. The last extension in 2008. Together they allow SQL databases to be self-describing (I think no vendor has attained that) and the structures queryable in a standard way

In fact that is the main purpose: these views in INFORMATION_SCHEMA are a very convenient standard way to query – in a vendor agnostic way – about tables, views, columns, etc.

The table below is based on (thanks to this Stack Overflow answer on INFORMATION_SCHEMA):

• the text version of the SQL-92 standard
• the text version of the SQL-92 Persistent Stored Modules standard
• PostgreSQL: Documentation: 9.1: The Information Schema.
• Information Schema Views (Transact-SQL).
• Lorenzo Alberton – Articles – Updated articles to extract META informations from databases
  (who also wrote 4 nice articles on getting meta data from SQL Server, Oracle, InterBase/Firebird and PostgreSQL)

And here is the SQL script I used to test each view:

[sourcecode language=”sql”]
select * from INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS
select * from INFORMATION_SCHEMA.APPLICABLE_ROLES
select * from INFORMATION_SCHEMA.ASSERTIONS
select * from INFORMATION_SCHEMA.ATTRIBUTES
select * from INFORMATION_SCHEMA.CHARACTER_SETS
select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
select * from INFORMATION_SCHEMA.COLLATIONS
select * from INFORMATION_SCHEMA.COLUMNS
select * from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES
select * from INFORMATION_SCHEMA.COLUMN_USER_DEFINED_TYPE_USAGE
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
select * from INFORMATION_SCHEMA.DIRECT_SUPERTABLES
select * from INFORMATION_SCHEMA.DIRECT_SUPERTYPES
select * from INFORMATION_SCHEMA.DOMAINS
select * from INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
select * from INFORMATION_SCHEMA.DOMAIN_USER_DEFINED_TYPE_USAGE
select * from INFORMATION_SCHEMA.ENABLED_ROLES
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
select * from INFORMATION_SCHEMA.METHOD_SPECIFICATIONS
select * from INFORMATION_SCHEMA.METHOD_SPECIFICATION_PARAMETERS
select * from INFORMATION_SCHEMA.MODULES
select * from INFORMATION_SCHEMA.PARAMETERS
select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
select * from INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS
select * from INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS
select * from INFORMATION_SCHEMA.ROLE_TABLE_GRANTS
select * from INFORMATION_SCHEMA.ROLE_USAGE_GRANTS
select * from INFORMATION_SCHEMA.ROLE_USER_DEFINED_TYPE_GRANTS
select * from INFORMATION_SCHEMA.ROUTINES
select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
select * from INFORMATION_SCHEMA.ROUTINE_COLUMN_USAGE
select * from INFORMATION_SCHEMA.ROUTINE_PRIVILEGES
select * from INFORMATION_SCHEMA.ROUTINE_TABLE_USAGE
select * from INFORMATION_SCHEMA.SCHEMATA
select * from INFORMATION_SCHEMA.SQL_FEATURES
select * from INFORMATION_SCHEMA.SQL_IMPLEMENTATION_INFO
select * from INFORMATION_SCHEMA.SQL_PACKAGES
select * from INFORMATION_SCHEMA.SQL_SIZING
select * from INFORMATION_SCHEMA.SQL_SIZING_PROFILES
select * from INFORMATION_SCHEMA.SQL_LANGUAGES
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
select * from INFORMATION_SCHEMA.TABLE_METHOD_PRIVILEGES
select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES
select * from INFORMATION_SCHEMA.TRANSFORMS
select * from INFORMATION_SCHEMA.TRANSLATIONS
select * from INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS
select * from INFORMATION_SCHEMA.TRIGGERS
select * from INFORMATION_SCHEMA.TRIGGER_COLUMN_USAGE
select * from INFORMATION_SCHEMA.TRIGGER_TABLE_USAGE
select * from INFORMATION_SCHEMA.TYPE_INFO
select * from INFORMATION_SCHEMA.USAGE_PRIVILEGES
select * from INFORMATION_SCHEMA.USER_DEFINED_TYPE_PRIVILEGES
select * from INFORMATION_SCHEMA.USER_DEFINED_TYPES
select * from INFORMATION_SCHEMA.VIEWS
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
[/sourcecode]

–jeroen