Re: Count Fields of a Table
Hi David If you are using mysql 5.0 and up, you can select from the INFORMATION_SCHEMA database to get this information and much more. Following is an example using a database called test and a table called t To get the column names, use SELECT column_name FROM information_schema.columns WHERE table_schema='test' AND table_name='t'; mysql use test; Database changed mysql describe t; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | TransactionDate | datetime | YES | | | | | amount | float| YES | | | | +-+--+--+-+-+---+ 2 rows in set (0.07 sec) mysql select * from information_schema.columns where table_schema='test' and table_name='t'; +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ | | test | t | TransactionDate | 1|| YES | datetime | NULL | NULL | NULL | NULL ||| datetime || | select,insert,update,references || | | test | t | amount | 2|| YES | float | NULL | NULL | 12 | NULL ||| float || | select,insert,update,references || +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ 2 rows in set (0.01 sec) mysql select count(*) from information_schema.columns where table_schema='test' and table_name='t'; +--+ | count(*) | +--+ | 2| +--+ 1 row in set (0.32 sec) mysql select column_name from information_schema.columns where table_schema='test' and table_name='t'; +-+ | column_name | +-+ | TransactionDate | | amount | +-+ 2 rows in set (0.08 sec) Douglas Sims [EMAIL PROTECTED] On Sep 22, 2006, at 9:54 AM, davidvaz wrote: Hello, Is there any way to find out, using only plain SQL, the number of fields of a given table. describe gives me the number of fields as result, but I need to get only that. Is it possible? Is it also possible to get only the fields name? Thanks David -- 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: Count Fields of a Table
David, For the count of columns in a table: SELECT count(information_schema.columns.column_name) FROM information_schema.columns WHERE information_schema.columns.table_schema = 'database_name' ANDinformation_schema.columns.table_name = 'table_name' For the names of the columns in a table: SELECT information_schema.columns.column_name FROM information_schema.columns WHERE information_schema.columns.table_schema = 'database_name' ANDinformation_schema.columns.table_name = 'table_name' Hope this helps. Randall Price Microsoft Implementation Group Secure Enterprise Computing Initiatives Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: davidvaz [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 10:54 AM To: mysql@lists.mysql.com Subject: Count Fields of a Table Hello, Is there any way to find out, using only plain SQL, the number of fields of a given table. describe gives me the number of fields as result, but I need to get only that. Is it possible? Is it also possible to get only the fields name? Thanks David -- 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: Count Fields of a Table
[...] Is there any way to find out, using only plain SQL, the number of fields of a given table. describe gives me the number of fields as result, but I need to get only that. Is it possible? Is it also possible to get only the fields name? AFIK there's no easy way to accomplish this using just plain SQL. Every time you use DESCRIBE tbl_name you get the number of columns counted as rows. You can also use SHOW COLUMNS FROM tbl_name SHOW FIELDS FROM tbl_name And from the shell you can also type mysqlshow -u myuser -p db_name tbl_name -- Iván Alemán ~ [[ Debian (Sid) ]] ~ -BEGIN GEEK CODE BLOCK- Version: 3.12 G!GCM d+ s: a? C+++ UL++ P L$ E--- W+++ N* o--- K- w O- M+ V-- PS++ PE-- Y PGP+++ t-- 5 X R+ !tv b++ DI-- D+++ G+ e++ h* r+ z**$ --END GEEK CODE BLOCK-- bonovoxmofo.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count Fields of a Table
Hello, @Mr. Price and Mr. Sims Thank you for show me the way, your tips where very educational. Thanks again. -- Iván Alemán ~ [[ Debian (Sid) ]] ~ -BEGIN GEEK CODE BLOCK- Version: 3.12 G!GCM d+ s: a? C+++ UL++ P L$ E--- W+++ N* o--- K- w O- M+ V-- PS++ PE-- Y PGP+++ t-- 5 X R+ !tv b++ DI-- D+++ G+ e++ h* r+ z**$ --END GEEK CODE BLOCK-- bonovoxmofo.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
