Check if a table exists in a mysql database

[graciano]

Hi,
After connectig to a database i whant to check if a database is already created and with a specific structure.
As an example:mysql> describe Teste;


+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| field01 | int(11) | NO | PRI | NULL | |
| field02 | int(11) | YES | | NULL | |
+---------+---------+------+-----+---------+-------+


What sort of query showld i use?

Thanks

[spirit]

read this (http://dev.mysql.com/doc/refman/5.0/en/create-table.html).
you should use CREATE TABLE IF NOT EXISTS.

[graciano]

I know about create table if not exists ... but that's not the point.
It ould create the table, when i just whant to know if it exists, and with a specific structure ;)

[MikeG]

Query that table with

SELECT field1, field2, (for all fields) FROM Teste

If you get an error, you know it doesn't exist or a field within the table doesn't exist (checks structure and existence). As far as testing each field's attributes, I don't know off hand how to go that far with it.

[Lesiok]

RTFM look at SHOW statements (http://dev.mysql.com/doc/refman/5.4/en/show-columns.html) in MySQL manual

[graciano]

Well ... if we take the RTFM strictly then we could close the forum.
It is all in the Manual ... just take the time to read all of them :)
Any way ... thanks for nothing.

[kwotski]

Maybe he's trying to point you at "show create table <tablename>"?

You can do:

show tables like '<tablename>'

first to get an idea whether the table exists.

TBH I've never used these in code, only at the mysql prompt, so I don't know how you would go about parsing the table structure from the output of show create table, but maybe it will get you going..

[graciano]

I think that the best approach for what i pretend will be looking directly into the information_schema database and then just look into the right table.
Something like this:


mysql> select COLUMN_NAME, DATA_TYPE from COLUMNS where TABLE_NAME = "T1";
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| a | int |
| b | int |
| x | int |
+-------------+-----------+

This will require special privileges for the user but ... if i just grant the right privileges there will be no problem.

[Netwiz]

If you want to use the builtin stuff in QT, you can use QSqlDatabase::tables() once you have opened the database and check if the table is in the QStringList returned.

Then you can use QSqlRecord QSqlDatabase::record ( const QString & tablename ) to get the table structure.