From: Peter Brawley
Date: May 14 2007 1:12am
Subject: Re: SELECT statement returning columns for a given table?
List-Archive: http://lists.mysql.com/mysql/206745
Message-Id: <4647B792.5080207@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >I know about "SHOW COLUMNS FROM tabname", but am looking for something
 >more SQL-y, because I want to use the results as part of a larger SQL
 >statement. For example, I want to find all the tables in a given db
 >that don't have a column named 'timestamp'.

 >I thought the 'mysql' system db may have this info, but it doesn't 
seem to.

In 5.0 & later MySQL implements the ISO SQL spec for information_schema, 
which does that.

PB

-----

Kelly Jones wrote:
> In PostgreSQL, the following SELECT statement will return all the
> columns for 'tabname':
>
> SELECT x.attname FROM pg_attribute x, pg_class y WHERE
> x.attrelid=y.oid AND relname='tabname';
>
> Does MySQL have anything similar?
>
> I know about "SHOW COLUMNS FROM tabname", but am looking for something
> more SQL-y, because I want to use the results as part of a larger SQL
> statement. For example, I want to find all the tables in a given db
> that don't have a column named 'timestamp'.
>
> I thought the 'mysql' system db may have this info, but it doesn't 
> seem to.
>