Close

SQL server list tables: How to show all tables

Posted by: AJ Welch

Depending on the version of SQL Server you are running, the method for querying and returning a list of all user-created tables may differ slightly. Below we’ll briefly examine the TSQL statements that can be used to retrieve a table list for both the older SQL Server 2000 and the newer 2005 version or beyond.


Listing tables in SQL server 2000


For older versions of SQL Server (such as SQL Server 2000, though this method is also supported in SQL Server 2005 for backward compatibility), you’ll need to query the SYS.SYSOBJECTS metadata view. SYS.SYSOBJECTS contains a row for every object that has been created in the database, including stored proceduresviews, and user tables (which are an important to distinguish from system tables.)

The SYSOBJECTS table houses a couple dozen columns of data since it must hold information about virtually everything added to the server over time. Therefore, to find a list of user-created tables (thus ignoring system tables), we’ll need to find results where the xtype column (which specifies the object type for that row) is equal to the value U, which stands for user table. The resulting TSQL statement should look like this:

SELECT
  *
FROM
  SYSOBJECTS
WHERE
  xtype = 'U';
GO

Note: Since SYSOBJECTS are part of the SYS namespace, when querying SYSOBJECTS it is unnecessary to specify that SYSOBJECTS is part of the global SYS namespace, so we can omit it as shown in the example above.

This will return a result list of all user-created tables. Since the amount of information returned when querying all columns is quite large, you may wish to trim the results by viewing only the name column and perhaps the crdate (date of creation):

SELECT
  name,
  crdate
FROM
  SYSOBJECTS
WHERE
  xtype = 'U';
GO

Listing tables in SQL server 2005 or newer


Listing all the tables in SQL server when using a newer version (SQL 2005 or greater) is a matter of querying the INFORMATION_SCHEMA views which are automatically built into SQL Server. These allow you to easily view a wide variety of metadata for this particular SQL Server instance, including information about COLUMNSROUTINES, and even TABLES.

You may notice that there are four columns returned when using the INFORMATION_SCHEMA.TABLES view, but the most important column is TABLE_TYPE, which determines whether the table in that row is an actual table (BASE TABLE) or a view (VIEW).

To return all tables and views in one query, execute the following TSQL statement:

SELECT
  *
FROM
  INFORMATION_SCHEMA.TABLES;
GO

It may also be wise to specify the database you wish to query:

SELECT
  *
FROM
  databaseName.INFORMATION_SCHEMA.TABLES;
GO

If you only wish to retrieve actual tables and filter out views from the results, add a WHERE TABLE_TYPE = 'BASE TABLE' clause:

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