Utilizing Information Schema for Database Migration

SQL-92 is a milestone in setting up the standards of SQL query language for relational database. In SQL-92, an important feature is to define Information_Schema – a series of read-only views for retrieving database metadata.

Information_Schema is quite useful when porting one SQL-92 compatible database to another, especially when you have large number of backend scripts which involve metadata operations.

Information_Schema may bring lots of convenience when we build up database-driven solutions since usually these solutions use database metadata quite often.

Let’s see which major databases support SQL-92 Information_Schema :

RDBMSRankSQL-92 CompatibleInformation_schema
Oracle1stOracle data dictionary: desc dict;
Open source project: ora-info-schema
MySQL 5+2ndMySQL Info Schema

MySQL 5.1 Clickable ER Info Schema
MS SQL Server 7+3rdMS SQL Server Info Schema
PostgreSQL 7.4+4thPostgreSQL Info Schema
IBM DB25th
SQLite7thSQLite Info Schema
MariaDB11thMariaDB Info Schema
H230thH2 Info Schema

It’s a notable exception that Oracle, as No 1 relational database, doesn’t implement Information_Schema based on SQL-92. But luckily, most major databases are SQL-92 compatible, such as MySQL, MS SQL Server and PostgreSQL.

Should you want to learn more about database migration, please refer to Database Migration or contact us.