Yesterday I blogged about how to document a MySQL database by a rather involved process of importing the database into SQL Server and then Microsoft Access. Actually you could simply use phpMyAdmin 2.5.0 to display the same information by clicking the Structure and then the Data Dictionary links. However importing a MySQL database into SQL Server and Microsoft Access is still a useful task.
phpMyAdmin 2.5.0 probably queries the information_schema table to obtain its information about the database design. The information_schema is a special database table that contains data about all the other databases and tables on the database server. Querying the information_schema table can be very useful for dynamically generating code. You could construct tedious INSERT and UPDATE SQL statements or stored procedures based on this information. I have found the correct syntax for running information_schema queries on SQL Server and MySQL:
SQL Server Queries
SELECT table_name FROM information_schema.tables WHERE table_catalog = ‘dbName’
tables in a database
SELECT column_name, data_type, numeric_precision, character_maximum_length,is_nullable, column_default
FROM information_schema.columns
WHERE table_name = ‘tableName’
columns in a database
MySQL Queries
select table_name, create_time from information_schema.tables where table_schema = ‘books’
tables in a database
select column_name, data_type, numeric_precision, character_maximum_length,is_nullable, column_default from information_schema.columns where table_name = ‘reading’
columns in a database
