SEO

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

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
Doug@Apley.com



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/mysql?unsub=DSims@apley.com
>

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'
AND information_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'
AND information_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: Randall.Price@vt.edu
Phone: (540) 231-4396

-----Original Message-----
From: davidvaz [mailto:davidvaz@ncc.up.pt]
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/mysql?unsub=Randall.Price@vt.edu

[...]
> 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

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