Here's a list of the information schema views: Apache Derby doesn't support the INFORMATION_SCHEMA views because they prefer to simply implement the JDBC Driver's getMetaData() method.
The cfdbinfo actually uses the JDBC Driver's getMetaData() method (this is part of the JDBC Standard that Drivers implement this method). Ofcourse if you are using ColdFusion 8, you can use the new cfdbinfo tag to get the same column information. And if a column is made wider, you don't have to make any code changes. 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. I have been using the INFORMATION_SCHEMA views to build some automatic datatype validation. Apache Derby - NOT Supported As of Version 10.3.
Oracle - Does not appear to be supported.PostgreSQL - Supported in Version 7.4 and up.Microsoft SQL Server - Supported in Version 7 and up.
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: Because the structure of these tables are standardized you can write SQL statements that work on various database platforms.įor 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 The INFORMATION_SCHEMA views provide meta data information about the tables, columns, and other parts of your database. 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.