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    |       | 
+---------+---------+------+-----+---------+-------+

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

To copy to clipboard, switch view to plain text mode 

What sort of query showld i use?

Thanks

[spirit]

read this.
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 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       | 
+-------------+-----------+

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

To copy to clipboard, switch view to plain text mode 

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.