[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 xxxxx - returns a table containing a SQL > statement you > > > could use to recreate the table xxxxx if you needed to (including all > > > indexes and constraints) > > > SHOW COLUMNS FROM xxxxx - 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]
