Pin Me

How to Get a List of Table Names in SQL

written by: Karishma Sundaram•edited by: Michele McDonough•updated: 5/28/2010

Tables are an important part of any database structure. When programming, it can sometimes be important to retrieve a list of table names in SQL, especially if the database schema is not visually available for review. This article teaches you how to find SQL table names.

  • slide 1 of 3

    A Brief Introduction to Structured Query Language

    SQL Before learning how to find SQL table names, it's best to know a bit about SQL. SQL, or Structured Query Language, is a collection of commands used to manipulate databases and their contents. It is a generic language which is used by a number of databases, regardless of the developer. Therefore, as many software applications require databases for functioning, it is a valuable skill to have.

    SQL is formed by simple syntaxes, made of keywords and parameters. These parameters are usually identifiers within the database itself. The commands are easy to remember and implement, as they are merely logical statements without the complicated rules that are usually seen in programming language constructs.

  • slide 2 of 3

    Structure of a Relational Database

    To use SQL effectively, it is important to understand the basic structure of a database known as a schema. The database has a definite structure with rigid rules for storing data, which makes retrieval efficient.

    A relational database is essentially a multi-dimensional schema. Because this is difficult to implement using programming, it is broken down into two-dimensional tables and relationships between those tables. Each table is made up of records or tuples that have attributes. The records can be visualized as rows, whereas the attributes are usually values under different column headings.

    Because there are multiple records with various attributes, there must be at least one instance that uniquely identifies one record from all the others. This is known as the primary key and it is a compulsory part of any table. A primary key can be composed of a single, or a combination of two or more attributes; the only condition is that the result must be unique for every row. Next up, learn how to find SQL table names, utilizing a variety of different formats.

  • slide 3 of 3

    Retrieving a List of Tables using SQL

    Information (table names) is stored in as metadata of the database schema. Each database vendor stores the information about their schema in different ways. Therefore, to be able to get a list of table names in SQL, knowledge about the system tables of the database being used is required.

    The following examples illustrate how to get a list of table names in SQL, specific to each database vendor:

    1. Oracle:

    SELECT * FROM all_tables;

    SELECT * FROM user_tables;

    Both ‘all_tables’ and ‘user_tables’ are system tables in Oracle. The first SQL statement will get a list of all the table names, whereas the second command will only show the user-created tables, by filtering out the system tables altogether. The SELECT command is used with the wildcard ‘*’ to indicate all the records should be selected.

    2. MySQL:

    SHOW TABLES;

    In MySQL, there is a specific command to retrieve table names. The full syntax of the command allows filters to be applied to the results, which eliminates various records from the results.

    3. MSSQL:

    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

    In MSSQL, the meta data is stored in the INFORMATION_SCHEMA. To retrieve information about tables in particular, the stored procedure TABLES is invoked on the INFORMATION_SCHEMA. An additional qualifier of TABLE_TYPE is applied, so that only actual tables are displayed in the results, as opposed to other elements of the schema which are stored in the form of tables.

    4. PostGRES:

    SELECT * FROM pg_tables

    Similar to the Oracle syntax, a list of table names in PostGRES can be retrieved using the SELECT statement as well.