Every now and then I find myself typing SHOW TABLES in SQL Server, expecting to get a list of tables.

That would make perfect sense if I was using MySQL or MariaDB. But SQL Server/T-SQL doesn’t have a SHOW TABLES statement like MySQL or MariaDB, so it never works. And I keep forgetting. But fortunately, SQL Server does have alternatives.

Here are five options for getting a list of tables in SQL Server. These can be used whenever you’re trying to find that elusive SHOW TABLES statement in SQL Server.

Option 1 – sys.tables

The sys.tables system catalog view is designed specifically for returning user table information:

SELECT name
FROM sys.tables;

This returns the name of all user tables in the current database. You can switch to the correct database by prepending the statement with USE <database name> where <database name> is the name of the database for which tables you want listed.

For example, the following statement returns all user tables from the KrankyKranes database:

USE KrankyKranes;
SELECT name
FROM sys.tables;

You can also return the schema if you want:

SELECT 
    SCHEMA_NAME(schema_id) AS schema_name,
    name AS table_name
FROM sys.tables;

Here, I used the SCHEMA_NAME() function to translate the value of the schema_id column into the actual name of the schema.

Option 2 – sys.objects

You can also use the sys.objects system catalog view. If you choose this option, you can filter it by type so that only user tables are returned:

SELECT name 
FROM sys.objects 
WHERE type = 'U';

This returns user tables from the current database. This view also contains a schema_id column, so you can use the SCHEMA_NAME() function to translate that into the schema name if required.

Option 3 – information_schema.tables

The INFORMATION_SCHEMA.TABLES view returns one row for each table or view in the current database for which the current user has permissions.

We can filter this view by type so that only base tables are returned:

SELECT 
    TABLE_SCHEMA,
    TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'Base Table';

The TABLE_SCHEMA column contains the table schema name, and so we don’t need to use the SCHEMA_NAME() function to convert the schema ID to its name.

Option 4 – sp_tables

The sp_tables stored procedure returns a list of objects that can be queried in the current environment. This means any table or view, except synonym objects.

The quickest and easiest way to call this procedure is like this:

sp_tables;

But that can return a long list of objects.

It’s usually better to be more specific. For example:

EXEC sp_tables
    @table_owner = 'dbo', 
    @table_qualifier = 'KrankyKranes', 
    @table_type = "'TABLE'";

Here, I’m telling the procedure to show user tables in the KrankyKranes database that are owned by dbo.

The @table_type argument accepts a comma separated list of values. So if we wanted to include views, we could do the following:

EXEC sp_tables
    @table_owner = 'dbo', 
    @table_qualifier = 'Music', 
    @table_type = "'TABLE', 'VIEW'";

Option 5 – dbo.sysobjects

Prior to SQL Server 2005, the dbo.sysobjects view was the view for showing tables and other objects.

So if you’re using an old edition of SQL Server, you can do this:

SELECT name 
FROM dbo.sysobjects
WHERE xtype = 'U';