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 procedures
, views
, 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 COLUMNS
, ROUTINES
, 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