From: Peter Brawley Date: August 22 2006 8:48pm Subject: Re: Finding field in all tables List-Archive: http://lists.mysql.com/mysql/201241 Message-Id: <44EB6D92.8060509@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-44EB6D930AA9=======" --=======AVGMAIL-44EB6D930AA9======= Content-Type: multipart/alternative; boundary=------------030201030404080607030808 --------------030201030404080607030808 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Jerry />I haven't ventured into MySQL 5.x, I have enough trouble working with this >house of cards I was handed. I'll try to remember this for the future, >though./ All you need to do is install mysql 5 in any box, run the dump script, then execute one query: SELECT LOWER(table_name) AS 'Table', LOWER(column_name) AS 'Column', ordinal_position As 'Ordinal Position', column_default AS 'Default', UPPER(data_type) AS 'Type', character_maximum_length AS 'Max Len', character_octet_length AS 'Octet Len', numeric_precision AS 'Precision', numeric_scale AS 'Numeric Scale', is_nullable AS 'Null', character_set_name AS 'Character Set', collation_name AS 'Collation' FROM information_schema.columns WHERE table_schema = 'yourdbname' ORDER BY table_name,'Ordinal Position'; and there are all your tables with all their columns. Absent a modelling tool, there's not a quicker way to get that info. PB ----- Jerry Schwartz wrote: > I haven't ventured into MySQL 5.x, I have enough trouble working with this > house of cards I was handed. I'll try to remember this for the future, > though. > > Thanks to all. > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > > > > > > --------------030201030404080607030808 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Jerry

>I haven't ventured into MySQL 5.x, I have enough trouble working with this
>house of cards I was handed. I'll try to remember this for the future,
>though.

All you need to do is install mysql 5 in any box, run the dump script, then execute one query:

SELECT
  LOWER(table_name) AS 'Table',
  LOWER(column_name) AS 'Column',
  ordinal_position As 'Ordinal Position',
  column_default AS 'Default',
  UPPER(data_type) AS 'Type',
  character_maximum_length AS 'Max Len',
  character_octet_length AS 'Octet Len',
  numeric_precision AS 'Precision',
  numeric_scale AS 'Numeric Scale',
  is_nullable AS 'Null',
  character_set_name AS 'Character Set',
  collation_name AS 'Collation'
FROM information_schema.columns
WHERE table_schema = 'yourdbname'
ORDER BY table_name,'Ordinal Position';

and there are all your tables with all their columns. Absent a modelling tool, there's not a quicker way to get that info.

PB

-----

Jerry Schwartz wrote: 
I haven't ventured into MySQL 5.x, I have enough trouble working with this
house of cards I was handed. I'll try to remember this for the future,
though.

Thanks to all.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
--------------030201030404080607030808-- --=======AVGMAIL-44EB6D930AA9======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.5/425 - Release Date: 8/22/2006 --=======AVGMAIL-44EB6D930AA9=======--