List:General Discussion« Previous MessageNext Message »
From:Dave Merrill Date:January 13 2005 12:00am
Subject:RE: Getting info about db columns
View as plain text  
[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
> >

Thread
Getting info about db columnsDave Merrill12 Jan
  • Re: Getting info about db columnsSGreen12 Jan
  • Re: Getting info about db columnsRhino12 Jan
  • Re: Getting info about db columnsPeter Brawley13 Jan
RE: Getting info about db columnsDave Merrill13 Jan