INFORMATION_SCHEMA Support in MySQL, PostgreSQL
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.
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
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
Here's a list of the information schema views:
- SQL to Select a random row from a database table - September 14, 2005
- SQL Reserved Key Words Checker Tool - March 28, 2005
- Top 10 Reserved SQL Keywords - October 28, 2008
- Backwards LIKE Statements - January 10, 2007
- LIMIT and OFFSET SQL Pagination - August 29, 2005
- Docker Container exited with code 137
- Why is my cron.daily script not running?
- Announcing FuseGuard Version 3
- CFSummit 2017
- Java Unlimited Strength Crypto Policy for Java 9 or 1.8.0_151
- Java 9 Security Enhancements
- Upcoming CFML Conferences in April 2017
- CFSummit 2016 Slides