Getting info about db columns
Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting info about db columns
May I introduce you to the family of SHOW commands? SHOW DATABASES - lists all databases on a server SHOW TABLES - lists all tables within your current database SHOW CREATE TABLE x - returns a table containing a SQL statement you could use to recreate the table x if you needed to (including all indexes and constraints) SHOW COLUMNS FROM x - returns a table similar to INFORMATION_SCHEMA.columns but only for the table you specify. and the list goes on and on: http://dev.mysql.com/doc/mysql/en/SHOW.html Alas, the INFORMATION_SCHEMA views are not production ready. They are being added to MySQL with the 5.x+ branch of development, which is not yet stable. Be patient, many good things are coming with v5. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Dave Merrill [EMAIL PROTECTED] wrote on 01/12/2005 04:07:16 PM: Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting info about db columns
In addition to Shawn's suggestions, the other approach you could take would be to write Java programs that make use of the DataBaseMetaData and ResultSetMetaData interfaces. I've used this approach with success, although not to any great extent yet. Of course, if you don't know Java or don't approve of having to write applications to get this sort of information, Shawn's approaches are going to be better for you ;-) Rhino - Original Message - From: Dave Merrill [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Wednesday, January 12, 2005 4:07 PM Subject: Getting info about db columns Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- 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: Getting info about db columns
[Sending this to this list, just for general reference, since I didn't notice that Reply on this list goes to the poster, not the list. No other list I'm on works that way, so I plead Failure To Open Eyes.] Dave Merrill Dave Merrill wrote on 01/12/2005 04:56:34 PM: Thanks Shawn, looks like the stuff. SHOW TABLES is exactly one of my answers. SHOW COLUMNS from dbname like '%name%' is useful in that it can restrict by column name, but am I right that it can't restrict by type or keyed-ness, or span more than one table? That's correct, SHOW COLUMNS only works for one table at a time and the like option only restricts by name. Also, it looks like if you wanted to find out the pk columns in a set of tables, SHOW INDEX would need to be called separately from the column listing, and separately for each table. Actually, I prefer the results of SHOW CREATE TABLE as it shows you not only which columns are in the PRIMARY KEY but in which order they are listed. That command also is the only one that gives me good information about FOREIGN KEYs as well. Good news about INFORMATION_SCHEMA too, sounds like a good thing. Do you know if the goal is to be syntax compatible w SQL Server? Is there a std spec for this? I thought it was a microsoft idiosyncracy, though, for once, one I liked. Yes, the INFORMATION_SCHEMA views are part of every SQL standard (at least as far back as SQL92) so, if M$ wasn't too full of themselves, the results should be comparable. However, I haven't done a side-by-side comparison yet so I can't tell you for sure. Thanks again, Dave You're most welcome, Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS. It's a good habit to hit REPLY TO ALL (or whatever your email client uses) so that the list is always copied (cc: -ed) on any responses. It performs two basic functions: everyone gets to share in the conversation so the knowledge gets spread around and you will generally get more responses. May I introduce you to the family of SHOW commands? SHOW DATABASES - lists all databases on a server SHOW TABLES - lists all tables within your current database SHOW CREATE TABLE x - returns a table containing a SQL statement you could use to recreate the table x if you needed to (including all indexes and constraints) SHOW COLUMNS FROM x - returns a table similar to INFORMATION_SCHEMA.columns but only for the table you specify. and the list goes on and on: http://dev.mysql.com/doc/mysql/en/SHOW.html Alas, the INFORMATION_SCHEMA views are not production ready. They are being added to MySQL with the 5.x+ branch of development, which is not yet stable. Be patient, many good things are coming with v5. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting info about db columns
SHOW TABLES lists tables in a db DESCRIBE tablename (= SHOW CREATE TABLE tablename) outputs column info, but your application will have to parse it. EXPLAIN is for query optimisation analysis, not table column listings. The manual's pretty clear on these topics. PB - Dave Merrill wrote: Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
