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<\/b> to display the same information by clicking the Structure<\/i> and then the Data Dictionary<\/i> links. However importing a MySQL database into SQL Server and Microsoft Access is still a useful task. <\/p>\n
phpMyAdmin 2.5.0<\/b> 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:<\/p>\nSQL Server Queries<\/h2>\n
SELECT table_name FROM information_schema.tables WHERE table_catalog = ‘dbName’
\ntables in a database<\/i><\/p>\n
SELECT column_name, data_type, numeric_precision, character_maximum_length,is_nullable, column_default
\nFROM information_schema.columns
\nWHERE table_name = ‘tableName’
\ncolumns in a database<\/i><\/p>\nMySQL Queries<\/h2>\n
select table_name, create_time from information_schema.tables where table_schema = ‘books’
\ntables in a database<\/i><\/p>\n
select column_name, data_type, numeric_precision, character_maximum_length,is_nullable, column_default from information_schema.columns where table_name = ‘reading’
\ncolumns in a database<\/i><\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading