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]
