INFORMATION_SCHEMA Support In MySQL, PostgreSQL

Tuesday, February 19, 2008 by Mistlee


Can't see any images? - !




Click to Play

Google Urchin Examined
The recent release of Google Urchin raises a few questions for Google. WebProNews Reporter Abby Prince compares Google Analytics and Google Urchin...

Recent Articles

Sun, MySQL Merger - Open Source Sinergy?
While Sun Microsystems was buying MySQL for $1 billion, Rome was guesting the international conference "Boosting innovation and growth by fostering Open Source Software trust and quality", organized by the...

Flex, AIR, And SQL
I decided to give myself a new AIR/Flex project, one that would specifically use the built-in database. My project was a simple one - a time tracker. I currently use Side Job Track to track all of my clients, projects, and hours.

ADO.NET Picks Up SQL Provider Support
IBM, MySQL, and other SQL database providers plan to support the ADO.NET Entity Framework, currently available as version Beta 3. Microsoft has touted ADO.NET as an easier mechanism for constructing data...

New SQL Server Backup Solution Developed By...
A new SQL Server backup solution has been developed by Cortex I.T. Labs (developers of BackupAssist) and it is said to be unique. The product apparently provides not only disaster recovery of critical data, but complete...

Single Quotes In A Query Issue
Alex had a problem with his SQL. This is actually a frequently asked question and I've covered it here before (I think so anyway), but I thought I'd mention it again. It comes up from time to time as people forget.


02.19.08

INFORMATION_SCHEMA Support In MySQL, PostgreSQL

By Pete Freitag

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

Try a Better Way Today. Try WebEx PCNow

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

Originally published at petefreitag.com

Comments


About the Author:
Pete Freitag ( http://www.petefreitag.com/) is a software engineer, and web developer located in central new york. Pete specializes in the HTTP protocol, web services, xml, java, and coldfusion. In 2003 Pete published the ColdFusion MX Developers Cookbook with SAMs Publishing.

Pete owns a Firm called Foundeo ( http://foundeo.com/) that specializes in Web Consulting, and Products for Web Developers.

About SQLproNews
SQLproNews is a collection of up to date tutorials and insightful articles designed to help SQL users of any skill level implement successful SQL systems and practices. SQL Strategies and Tactics for Business

SQLproNews is brought to you by:

SecurityConfig.com NetworkingFiles.com
NetworkNewz.com WebProASP.com
DatabaseProNews.com SQLProNews.com
ITcertificationNews.com SysAdminNews.com
SQLproNews.com WirelessProNews.com
CProgrammingTrends.com SysAdminNews.com





-- SQLProNews is an iEntry, Inc. publication --
iEntry, Inc. 2549 Richmond Rd. Lexington KY, 40509
2008 iEntry, Inc.  All Rights Reserved   Privacy Policy   Legal

advertising info | news headlines | free newsletters | comments/feedback | submit article


Unsubscribe from SQLProNews.
To unsubscribe from SQLProNews or any other iEntry publication, simply send an email request to: support@ientry.com
SQL Strategies and Tactics for Business SQLproNews News Archives About Us Feedback SQLproNews Home Page About Article Archive News Downloads WebProWorld Forums Jayde iEntry Advertise Contact

0 comments: