Re: How to get meta data info in MySQL
My database editor uses: describe name; Karen Chu wrote: I want to be able to get all table names in a database and all the column names and associated data type from a particular table. I wonder how to do that in MySQl. I understand I can use show tables to see the table info, but I want to be able to do 'select' in order to get the results back. I want to know if there is something like select name from sysobject in Sybase or select name from all_tables in Oracle. Thanks. I would appreciate any advice. Karen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get meta data info in MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: Thanks for the reply! Yes, I understand I can do show tables to show all table names in a database and I can do describe table_name to get all column information for a table. But in the application programming, I want to be able to do 'select' and be able to see table names and column info as a query result set. Can MySQL do that? There must be a place to store this information. In oracle or Sybase, these are store in system tables (actual table) so I can do 'select' from them to get a result set. I am new to MySQL. I wonder where these meta data stored in MySQL. Any thoughts are appreciated. Thanks! Either of those commands (show/describe) return the data in a result set form. Is there a reason that doesn't work for you? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5JnhtvXNTca6JD8RAkm2AKCNzJUSCLnrzXKvAMnmQ2W5cwzYoQCfVbPn okkkEY8/4XQL91gUyQw6zQw= =jZ+B -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get meta data info in MySQL
I wish to have something like 'select name from all_tables' in oracle or 'select name from sysobjects' in Sybase. Oracle has 'show tables' or 'describe table' too, but actual meta data is *stored* in systems tables. I wonder if MySQL also stores these in system tables or files or somewhere else. -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 7:30 AM To: Karen Chu Cc: 'gerald_clark'; [EMAIL PROTECTED] Subject: Re: How to get meta data info in MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: Thanks for the reply! Yes, I understand I can do show tables to show all table names in a database and I can do describe table_name to get all column information for a table. But in the application programming, I want to be able to do 'select' and be able to see table names and column info as a query result set. Can MySQL do that? There must be a place to store this information. In oracle or Sybase, these are store in system tables (actual table) so I can do 'select' from them to get a result set. I am new to MySQL. I wonder where these meta data stored in MySQL. Any thoughts are appreciated. Thanks! Either of those commands (show/describe) return the data in a result set form. Is there a reason that doesn't work for you? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5JnhtvXNTca6JD8RAkm2AKCNzJUSCLnrzXKvAMnmQ2W5cwzYoQCfVbPn okkkEY8/4XQL91gUyQw6zQw= =jZ+B -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get meta data info in MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: I wish to have something like 'select name from all_tables' in oracle or 'select name from sysobjects' in Sybase. Oracle has 'show tables' or 'describe table' too, but actual meta data is *stored* in systems tables. I wonder if MySQL also stores these in system tables or files or somewhere else. MySQL does not currently store this information in system tables. 'SHOW' / 'DESCRIBE' are treated as queries by MySQL (just a special case of a query), so I'm still confused as why they won't work for you. Is there a reason that you _have_ to use system tables for this functionality? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5KWHtvXNTca6JD8RAoI2AKDJoYzsvK2UEnA98ttB7wCm6GxOcwCgot7g IN4IyjvSI6aApNvx9tEYvHE= =VPrD -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get meta data info in MySQL
Thanks for the reply! Please see my pseudo code below. @@@ mark is the place where my questions come from. I wonder if it is doable in MySQL. I wish to have code like this: (@@@ is where my questions come from) // connect to the DB // list all table names in the dtabase print trtd bgcolor=#1A4D80 align=center$table Table/font/td/tr; // $query_all_tables = select name from all_tables ='$table'; $result_table_names = mysql_query($query_all_tables) or die(no tables were ever defined\n); $row_table_name = mysql_fetch_array($result_table_names); $table_in_db = $row_table_name[Tables in the DB]; ... // once a table is selected, let's try to get all fields information in this table //@@@ $query_all_fields - select column_name from all_columns where table_name ='$selected_table'; $result_field_names = mysql_quert($query_all_fields) or die(no fields?\n); $row_field_name = mysql_fetch_array($result_field_names); $field_in_table = $row_field_name[Fields in the Table]; -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 8:20 AM To: Karen Chu Cc: 'gerald_clark'; [EMAIL PROTECTED] Subject: Re: How to get meta data info in MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: I wish to have something like 'select name from all_tables' in oracle or 'select name from sysobjects' in Sybase. Oracle has 'show tables' or 'describe table' too, but actual meta data is *stored* in systems tables. I wonder if MySQL also stores these in system tables or files or somewhere else. MySQL does not currently store this information in system tables. 'SHOW' / 'DESCRIBE' are treated as queries by MySQL (just a special case of a query), so I'm still confused as why they won't work for you. Is there a reason that you _have_ to use system tables for this functionality? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5KWHtvXNTca6JD8RAoI2AKDJoYzsvK2UEnA98ttB7wCm6GxOcwCgot7g IN4IyjvSI6aApNvx9tEYvHE= =VPrD -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get meta data info in MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: Thanks for the reply! Please see my pseudo code below. @@@ mark is the place where my questions come from. I wonder if it is doable in MySQL. I wish to have code like this: (@@@ is where my questions come from) // connect to the DB // list all table names in the dtabase print trtd bgcolor=#1A4D80 align=center$table Table/font/td/tr; // $query_all_tables = select name from all_tables ='$table'; You could just replace this with 'SHOW TABLES' in MySQL, or if you need to match a particular table name or name pattern, you can add a LIKE clause at the end, see: http://www.mysql.com/doc/en/SHOW.html $result_table_names = mysql_query($query_all_tables) or die(no tables were ever defined\n); $row_table_name = mysql_fetch_array($result_table_names); $table_in_db = $row_table_name[Tables in the DB]; ... // once a table is selected, let's try to get all fields information in this table //@@@ $query_all_fields - select column_name from all_columns where table_name ='$selected_table'; Here you would just use 'SHOW COLUMNS FROM [tablename]' substituting whatever table name you wanted. -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5MJ5tvXNTca6JD8RAqyaAJ99dRIGebW0WYUFZsHTU9YHMbDF6gCfb39w Km5B97EbYIJVM30MUY/DUrQ= =Wo3Q -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get meta data info in MySQL
At 10:11 -0700 6/9/03, Karen Chu wrote: Thanks for the reply! Please see my pseudo code below. @@@ mark is the place where my questions come from. I wonder if it is doable in MySQL. I'm not sure why you wonder that. You've received several replies so far that indicate you cannot use SELECT for this purpose, and that you should use SHOW or DESCRIBE instead. Several people also have asked if there is some reason you cannot use SHOW/DESCRIBE, and you don't appear to have answered that question. Is there some reason? I wish to have code like this: (@@@ is where my questions come from) // connect to the DB // list all table names in the dtabase print trtd bgcolor=#1A4D80 align=center$table Table/font/td/tr; // $query_all_tables = select name from all_tables ='$table'; $result_table_names = mysql_query($query_all_tables) or die(no tables were ever defined\n); $row_table_name = mysql_fetch_array($result_table_names); $table_in_db = $row_table_name[Tables in the DB]; ... // once a table is selected, let's try to get all fields information in this table //@@@ $query_all_fields - select column_name from all_columns where table_name ='$selected_table'; $result_field_names = mysql_quert($query_all_fields) or die(no fields?\n); $row_field_name = mysql_fetch_array($result_field_names); $field_in_table = $row_field_name[Fields in the Table]; -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 8:20 AM To: Karen Chu Cc: 'gerald_clark'; [EMAIL PROTECTED] Subject: Re: How to get meta data info in MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: I wish to have something like 'select name from all_tables' in oracle or 'select name from sysobjects' in Sybase. Oracle has 'show tables' or 'describe table' too, but actual meta data is *stored* in systems tables. I wonder if MySQL also stores these in system tables or files or somewhere else. MySQL does not currently store this information in system tables. 'SHOW' / 'DESCRIBE' are treated as queries by MySQL (just a special case of a query), so I'm still confused as why they won't work for you. Is there a reason that you _have_ to use system tables for this functionality? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5KWHtvXNTca6JD8RAoI2AKDJoYzsvK2UEnA98ttB7wCm6GxOcwCgot7g IN4IyjvSI6aApNvx9tEYvHE= =VPrD -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
