INFORMATION_SCHEMA Support in MySQL, PostgreSQL

databases

I've known about the INFORMATION_SCHEMA views (or system tables) in SQL Server for a while, but I just leared recently that they are actually part of the SQL-92 standard and supported on other database platforms.

The INFORMATION_SCHEMA views provide meta data information about the tables, columns, and other parts of your database. Because the structure of these tables are standardized you can write SQL statements that work on various database platforms.

For example suppose you want to return a resultset with a list of all columns in a table called employees

SELECT table_name, column_name, is_nullable, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'employees'

Quite a handy feature, but it's hard to find what versions the of various database platforms started supporting this feature, here's a quick list:

  • Microsoft SQL Server - Supported in Version 7 and up
  • MySQL - Supported in Version 5 and up
  • PostgreSQL - Supported in Version 7.4 and up
  • Oracle - Does not appear to be supported
  • Apache Derby - NOT Supported As of Version 10.3

I have been using the INFORMATION_SCHEMA views to build some automatic datatype validation. With the INFORMATION_SCHEMA you can get the datatype, max character length, and if null values are allowed, and perform validation before it hits the database. And if a column is made wider, you don't have to make any code changes.

Ofcourse if you are using ColdFusion 8, you can use the new cfdbinfo tag to get the same column information. The cfdbinfo actually uses the JDBC Driver's getMetaData() method (this is part of the JDBC Standard that Drivers implement this method). Apache Derby doesn't support the INFORMATION_SCHEMA views because they prefer to simply implement the JDBC Driver's getMetaData() method.

Here's a list of the information schema views:

  • INFORMATION_SCHEMA.SCHEMATA
  • INFORMATION_SCHEMA.TABLES
  • INFORMATION_SCHEMA.COLUMNS
  • INFORMATION_SCHEMA.STATISTICS
  • INFORMATION_SCHEMA.USER_PRIVILEGES
  • INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
  • INFORMATION_SCHEMA.TABLE_PRIVILEGES
  • INFORMATION_SCHEMA.COLUMN_PRIVILEGES
  • INFORMATION_SCHEMA.CHARACTER_SETS
  • INFORMATION_SCHEMA.COLLATIONS
  • INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  • INFORMATION_SCHEMA.ROUTINES
  • INFORMATION_SCHEMA.VIEWS
  • INFORMATION_SCHEMA.TRIGGERS
  • INFORMATION_SCHEMA.PROFILING

Roland Bouman has created a clickable ER diagram of the INFORMATION_SCHEMA for MySQL (click on the table to go to the MySQL documentation for the table)



Related Entries

7 people found this page useful, what do you think?

Trackbacks

Trackback Address: 666/BC648AE43C774E2D52D12448BA6549E4

Comments

On 02/18/2008 at 8:29:10 AM UTC Roland Bouman wrote:
1
Hi!

Nice post, keep it up.

Can you please provide the links for your list of Information Schema compatibility.

I found:

MySQL 5.0: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

MS SQL 2005 http://msdn2.microsoft.com/en-us/library/ms186778.aspx

PostgreSQL 8.1 http://www.postgresql.org/docs/8.1/interactive/information-schema.html

However, I have been unable to locate this information for Oracle, and I was under the impression that Oracle does *not* provide an information_schema (although they do of course offer their own system views - the USER_%, DBA_% and ALL_% views)

kind regards,

Roland Bouman

On 02/18/2008 at 8:40:14 AM UTC Steve Bryant wrote:
2
This is really nice. I have really liked the INFORMATION_SCHEMA views in SQL Server. It will be nice when those views have more ubiquitous support and it looks like vendors are catching on.

On 02/18/2008 at 9:31:29 AM UTC Pete Freitag wrote:
3
Hi Roland,

http://www.postgresql.org/docs/7.4/static/release-7-4.html

I am trying to find something on Oracle's site, but it appears to be down? I found several web pages that mention 9i supporting it, but I will confirm and update if the information is incorrect.

Any easy way to find out is if anyone reading this has Oracle 9i installed?

On 02/18/2008 at 11:11:15 AM UTC Bill Karwin wrote:
4
Yes, the INFORMATION_SCHEMA is part of the SQL-92 standard. But unfortunately it's not practical to rely on it as a means of cross-database standardized queries against metadata.

- MySQL's implementation was unusably slow prior to 5.1.23, but they appear to have improved it. http://bugs.mysql.com/19588

- PostgreSQL's implementation of INFORMATION_SCHEMA is buggy. I have read that most people INFORMATION_SCHEMA and instead use the old PostgreSQL-proprietary system tables. http://www.postgresql.org/docs/8.2/static/catalogs.html

- Oracle has proprietary catalog tables, and does not support INFORMATION_SCHEMA. http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1.htm

IBM DB2 also uses proprietary catalog tables, and does not support INFORMATION_SCHEMA. http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0001063.htm

- InterBase/Firebird also uses proprietary catalog tables and does not support INFORMATION_SCHEMA. The InterBase manual shows how to create views against its catalog tables to match the spec for INFORMATION_SCHEMA relations.

On 02/18/2008 at 1:41:48 PM UTC pp wrote:
5
Yes, too bad Firebird doesn't come with those views as standard. But I have to disagree with Bill aobut PostgreSQL, it's information schema implementation is not buggy, it's just that people go with "true and tested" which means system tables (the same happens with mssql) and would happen with Oracle which right now doesn't support this standard.

On 02/18/2008 at 7:56:45 PM UTC Paul wrote:
6
Actually Oracle has had an information schema since at least version 7. Users are given views of the data in these schemas which depend upon their privileges.

Examples are user_tables for all tables owned by you, all_tables for all tables that you have access to and dba_tables for all tables.

These views are very comprehensive and would allow you to recreate any schema object.

On 02/19/2008 at 1:09:20 AM UTC Roland Bouman wrote:
7
Pete - no.

The point is that Oracle has indeed its own set of system views for metadata. That is nice and all, and the information you get from these is very rich, no complaints there. But it is *not* an implementation of the standard SQL feature called "information_schema" and you cannot simply port queries from one platform to the other.

On 02/19/2008 at 2:03:27 PM UTC Lorenzo Alberton wrote:
8
I've written a few tutorials on INFORMATION_SCHEMA and system tables for Firebird, Oracle, SQL Server and PostgreSQL:

http://www.alberton.info/firebird_sql_meta_info.html http://www.alberton.info/oracle_meta_info.html http://www.alberton.info/sql_server_meta_info.html http://www.alberton.info/postgresql_meta_info.html

HTH

On 03/12/2008 at 2:47:17 PM UTC dave wrote:
9
For anyone curious, you can query the sys tables in derby to get this info, see below for example:

select columnname, columnnumber, columndatatype, columndefault, autoincrementvalue, autoincrementstart, autoincrementinc from sys.syscolumns c, sys.systables t, sys.sysschemas s where c.referenceid = t.tableid and s.schemaid = t.schemaid and s.schemaname = 'APP' and t.tablename = 'AUDITS' order by columnnumber

On 09/30/2008 at 2:24:07 PM UTC Deepak Murthy wrote:
10
Hi There,

thank you all for this fruitful information. I am very new to DB. I but i was also trying to figure out how to get the schema name, that table belongs to? Because the same table name can be used under different schema's. It would be good if we retrieve the schema name also.

I tried the below query SELECT schema_name, table_name, column_name, is_nullable, data_type, character_maximum_length FROM INFORMATION_SCHEMA.Columns WHERE table_name = 'Sales'

Error: column "schema_name" does not exist

Thanks Deepak

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?