How to list the tables that we have created?

[vishalivadivelu]

how to list the tables that we have created ?

[mwasif]

This is the command to show all the tables inside a database.

[CODE=mysql]SHOW TABLES;[/CODE]

[dgreenhouse]

If you want to show all databases and tables and assuming you have access to MySQL's information_sch ema database, this query will list the databases and tables created. Note that "information_sc hema and mysql" are part of MySQL's engine. I've also instructed the query to not show tables associated with "phpmyadmin ."

SELECT table_schema,table_name FROM information_schema.tables
WHERE table_schema not in ("information_schema","phpmyadmin","mysql") order by table_schema,table_name

Note: table_schema is the database name

Also note:
The above query will only show databases with tables created.
If you want to show all databases (with or without created tables), you'd need to perform an "outer join" on the "schemata" table. The OUTER is typically optional with the RIGHT {JOIN} predicate.

i.e.

SELECT b.schema_name,a.table_name FROM information_schema.tables a
RIGHT OUTER JOIN schemata b on b.schema_name = a.table_schema
WHERE b.schema_name not in ("information_schema","phpmyadmin","mysql") order by b.schema_name,a.table_name