The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,119 other followers

INFORMATION_SCHEMA views in various databases

Posted by jpluimers on 2012/08/21

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

Two funny things about that standard:

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):

View 7 2000 2005 2008 2008 R2 2012
9.1.4
MariaDB  SQL-92   SQL:1999 SQL:2003 SQL:2006 SQL:2008 Notes
ADMINISTRABLE_ROLE_AUTHORIZATIONS ? ? ? ?
+
+
APPLICABLE_ROLES ? ? ? ?
+
+
ASSERTIONS ? ? ? ? ? + +
ATTRIBUTES ? ? ? ?
+
+
CHARACTER_SETS ? ? ? ?
+
+ +
CHECK_CONSTRAINTS ? + ? ? + ?
+
+ +
check_constraint_routine_usage ? ? ? ?
+
COLLATIONS ? ? ? ?
+
+ +
collation_character_set_applicability ? ? ? ?
+
COLUMNS ? + ? ? + ?
+
+ +
COLUMN_DOMAIN_USAGE ? + ? ? + ?
+
+ +
COLUMN_PRIVILEGES ? + ? ? + ?
+
+ +
COLUMN_USER_DEFINED_TYPE_USAGE ? ? ? ?
+
column_udt_usage

+
CONSTRAINT_COLUMN_USAGE ? + ? ? + ?
+
+ +
CONSTRAINT_TABLE_USAGE ? + ? ? + ?
+
+ +
data_type_privileges ? ? ? ?
+
DIRECT_SUPERTABLES ? ? ? ? +
DIRECT_SUPERTYPES ? ? ? ? +
DOMAINS ? + ? ? + ?
+
+ +
DOMAIN_CONSTRAINTS ? + ? ? + ?
+
+ +
DOMAIN_USER_DEFINED_TYPE_USAGE ? ? ? ?
+
domain_udt_usage

+
element_types ? ? ? ?
+
ENABLED_ROLES ? ? ? ?
+
+
foreign_data_wrapper_options ? ? ? ?
+
foreign_data_wrappers ? ? ? ?
+
foreign_server_options ? ? ? ?
+
foreign_servers ? ? ? ?
+
foreign_table_options ? ? ? ?
+
foreign_tables ? ? ? ?
+
KEY_COLUMN_USAGE ? + ? ? + ? + + +
METHOD_SPECIFICATIONS ? ? ? ? +
METHOD_SPECIFICATION_PARAMETERS ? ? ? ?
+
MODULES ? ? ? ? ? +
PARAMETERS ? + ? ? + ?
+
+ +
REFERENTIAL_CONSTRAINTS ? + ? ? + ?
+
+ +
ROLE_COLUMN_GRANTS ? ? ? ?
+
+
ROLE_ROUTINE_GRANTS ? ? ? ?
+
+
ROLE_TABLE_GRANTS ? ? ? ?
+
+
ROLE_USAGE_GRANTS ? ? ? ?
+
+
ROLE_USER_DEFINED_TYPE_GRANTS ? ? ? ?
+
ROUTINES ? + ? ? + ?
+
+ +
ROUTINE_COLUMNS ? + ? ? + ?
ROUTINE_COLUMN_USAGE ? ? ? ? +
ROUTINE_PRIVILEGES ? ? ? ?
+
+
ROUTINE_TABLE_USAGE ? ? ? ? +
SCHEMATA ? + ? ? + ?
+
+ +
sequences ? ? ? ?
+
SQL_FEATURES ? ? ? ?
+
+
SQL_IMPLEMENTATION_INFO ? ? ? ?
+
+
SQL_PACKAGES ? ? ? ?
+
+
sql_parts ? ? ? ?
+
SQL_SIZING ? ? ? ?
+
+
SQL_SIZING_PROFILES ? ? ? ?
+
+
SQL_LANGUAGES ? ? ? ?
+
+ +
TABLES ? + ? ? + ?
+
+ +
TABLE_CONSTRAINTS ? + ? ? + ?
+
+ +
TABLE_METHOD_PRIVILEGES ? ? ? ? +
TABLE_PRIVILEGES ? + ? ? + ?
+
+ +
TRANSFORMS ? ? ? ? +
TRANSLATIONS ? ? ? ? + +
TRIGGERED_UPDATE_COLUMNS ? ? ? ?
+

+
TRIGGERS ? ? ? ?
+
+
TRIGGER_COLUMN_USAGE ? ? ? ? +
TRIGGER_TABLE_USAGE ? ? ? ? +
TYPE_INFO ? ? ? ? +
USAGE_PRIVILEGES ? ? ? ?
+
+ +
USER_DEFINED_TYPE_PRIVILEGES ? ? ? ?
+
USER_DEFINED_TYPES ? ? ? ? +
user_mappings ? ? ? ?
+
user_mapping_options ? ? ? ?
+
VIEWS ? + ? ? + ? + + +
VIEW_COLUMN_USAGE ? + ? ? + ?
+
+ +
view_routine_usage ? ? ? ?
+
VIEW_TABLE_USAGE ? + ? ? + ?
+
+ +
 INFORMATION_SCHEMA Views   Count

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

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

–jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: