Listing all tables in a database

Is there a SQL command that will list all the tables in a database and which is provider independent (works on MSSQLServer, Oracle, MySQL)?

From stackoverflow

• No. They all love doing it their own little way.

• No, the SQL standard does not constrain where the table names are listed (if at all), so you'll have to perform different statements (typically SELECT statements on specially named tables) depending on the SQL engine you're dealing with.

  Tom H. : As Bill Karwin correctly points out, the INFORMATION_SCHEMA is part of the SQL-92 standard. Some vendors may not support it, but it is part of the standard.

• The closest option is to query the INFORMATION_SCHEMA for tables.

  SELECT *
  FROM INFORMATION_SCHEMA.Tables
  WHERE table_schema = 'mydatabase';
  

  The INFORMATION_SCHEMA is part of standard SQL, but not all vendors support it. As far as I know, the only RDBMS vendors that support it are:

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2000/2005/2008

  Some brands of database, e.g. Oracle, IBM DB2, Firebird, Derby, etc. have similar "catalog" views that give you an interface where you can query metadata on the system. But the names of the views, the columns they contain, and their relationships don't match the ANSI SQL standard for INFORMATION_SCHEMA. In other words, similar information is available, but the query you would use to get that information is different.

  (footnote: the catalog views in IBM DB2 UDB for System i are different from the catalog views in IBM DB2 UDB for Windows/*NIX -- so much for the Universal in UDB!)

  Some other brands (e.g. SQLite) don't offer any queriable interface for metadata at all.

  wcm : I was about to answer and say "No, absolutely not". Your answer saved me. I learned something new today :)

  Bill Karwin : I added links to the docs for INFORMATION_SCHEMA in the respective RDBMS brands that support it.

• If you are OK with using a non-SQL approach and you have an ODBC driver for the database and it implements the SQLTables entry-point, you possibly might get the information you want!

  pjjH

  details on the API at: http://msdn.microsoft.com/en-us/library/ms711831.aspx

• Here is a nice site about extracting METADATA

  • Firebird
  • Oracle
  • SQLServer
  • PostrgreSQL