CODEWITHSUNDEEP
sqlmysql
ZORRO_BLANCO
ZORRO_BLANCO

Reputation: 909

Return table names without using any of "show tables from database_name" or "select table_name from information_schema.tables" queries

I am trying to return table names without using "show tables from database_name" or "select table_name from information_schema.tables" queries the reason of that:

  1. I can't use "show tables from database_name" query because it returns a row set of table names with a fixed field name "Tables_database_name" and this is not acceptable in my code when the database name is too long -I am using DBExpress on Delphi and the column name can't be longer than 31 character-.

  2. I can't use "select table_name from information_schema.tables" query because it is not supported with old MySQL I think older than 5.1

Please help if any body knows:

Upvotes: 4

Views: 5191

Answers (1)

fsw
fsw

Reputation: 3695

How to change the resulted fixed column name of the "show tables from database_name".

Seems like SHOW TABLES is a separate statement with fixed syntax:

SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

http://dev.mysql.com/doc/refman/5.0/en/show-tables.html

It is not a SELECT so you can not UNION with it or juggle with it in any form (change column names) so i guess answer to this quesiotn is: it is impossible.

Any other query that returns the table names inside a specific database.

If you compare this: http://dev.mysql.com/doc/refman/4.1/en/show-tables.html

to this: http://dev.mysql.com/doc/refman/5.0/en/show-tables.html

It seems like only alternative to SHOW TABLES prior 5.0 was "mysqlshow" shell command

information_schema was introduced in MySql 5.0 and it was stable 8 years ago. So i guess you are trying to make your software compatible with very old versions.

What does this "DBExpress" do when column names are longer than 31 chars? does it truncate it or fails? Maybe you should state a question tagged "delphi" and "DBExpress" and ask how to bypass this limitation?

Upvotes: 1

Related Questions