How to Show/List Tables in a SQL Server Database

To list all tables in a SQL Server database, you can run the following query:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES  
WHERE TABLE_TYPE = 'BASE TABLE';

This command returns the names of all user-defined tables within the current database. As you can see, unlike MySQL or MariaDB, SQL Server does not support the SHOW TABLES statement directly. Instead, it provides several alternative methods to retrieve a list of tables, each with its own use cases and advantages.

In this article, we'll explore the different approaches, explain when to use them, and compare their benefits depending on your SQL Server version and requirements.

Why you need to list tables in SQL Server

Database developers and administrators have to monitor the condition of databases, including all associated objects and processes. Often, routine tasks such as database design, performance tuning, and security maintenance require the ability to view the list of tables within a particular database. Hence, database professionals search for reliable and efficient ways to accomplish this task accurately.

SQL Server system catalog views serve as the primary interface for accessing metadata, providing structured information about both schema objects (tables, views, procedures) and instance objects (logins, settings, access privileges). These views allow you to query metadata just like regular tables, making them the most reliable way to retrieve a list of all tables in a database.

Catalog views were introduced in SQL Server 2005 and remain the recommended method for metadata access because they offer consistent results, even when underlying system tables change. They also return descriptive column names, which help new users interpret the data.

The hierarchy of catalog views is organized into two tiers:

• Upper tier: sys.objects, which contains details about all schema-scoped objects in a database.
• Lower tier: views like sys.tables, sys.views, and sys.procedures, which inherit columns from sys.objects while adding their own specific attributes.

To list tables specifically, you can query the sys.tables view, provided you have the necessary permissions. Database owners have access by default, while other users may need explicit permissions to use this view.

How to list tables using sys.tables

You can retrieve all user tables in SQL Server using sys.tables. This system catalog view provides a complete list of user-defined tables in the current database:

SELECT 
  name 
FROM sys.tables;

By default, the query returns table names from the active database, but you can switch to another database context if needed. Keep in mind that access to sys.tables requires appropriate permissions. Database owners can query it without restrictions, while other users may need explicit privileges. Also note that sys.tables is unique to SQL Server and is not available in other RDBMSs like MySQL or PostgreSQL.

How to list tables using sys.objects

One of the most commonly used catalog views is sys.objects, which returns a list of user-defined tables along with other schema objects. To show user tables in SQL Server, you can filter the results by object type:

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

In this query, 'U' stands for user tables. Since sys.objects includes all schema-scoped objects (such as views, procedures, and constraints), applying the WHERE clause ensures that only tables are displayed. While sys.tables is more direct, sys.objects can be especially useful when you need to query multiple object types in one place.

Using INFORMATION_SCHEMA to list tables

The INFORMATION_SCHEMA.TABLES view is ANSI-compliant and lists all tables in a database, making it a portable option across different relational database systems. To display every table and view in the current database, you can use:

SELECT 
  * 
FROM INFORMATION_SCHEMA.TABLES;

If you want to show only user tables and exclude views, refine the query with a filter:

SELECT 
  TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';

This approach works in SQL Server 2005 and later. However, it may run slower than querying system catalog views, such as sys.tables or sys.objects, especially in large databases. Still, it remains a reliable and widely recognized method for listing tables in SQL Server when cross-platform compatibility is important.

Using dbo.sysobjects to list tables

dbo.sysobjects is an outdated version of the sys.objects view that was in use prior to the release of SQL Server 2005. If you are working with SQL Server 2000 or an earlier version and need to list tables in your databases, you should utilize dbo.sysobjects.

It provides the same results as sys.objects: the list of all objects in the database, including tables. When you need to list the database tables exclusively, you can modify the query in the same manner as in the modern version by adding the WHERE clause to filter object types:

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

Using the sp_tables stored procedure to list tables

The stored procedure sp_tables can be used to fetch a list of database objects, excluding synonym objects. The simplest way to query this stored procedure is:

sp_tables;

However, this approach may not be the most convenient option as it retrieves the entire list of database objects. For more precise results, you can modify the command to filter the output based on the table type, table name, table owner, and table qualifier.

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

While the sp_tables stored procedure can be useful, many SQL Server experts find it more limited and complex to use in larger queries compared to querying sys.tables and sys.objects.

Show tables in a specific schema

To list tables within a specific schema, you can filter results using the TABLE_SCHEMA column in the INFORMATION_SCHEMA.TABLES view. This is the simplest way to generate a SQL Server list of tables by schema query:

SELECT *  
FROM INFORMATION_SCHEMA.TABLES  
WHERE TABLE_SCHEMA = 'sales';
			

In this example, only tables that belong to the sales schema will be displayed. This method is especially useful in databases with multiple schemas, as it helps you quickly isolate objects that belong to a particular business area or application module.

Show temporary tables

To make SQL Server show temp tables, you can query the tempdb.sys.tables view and filter the results with a LIKE '#%' condition. This pattern ensures that only temporary objects are listed:

SELECT name  
FROM tempdb.sys.tables  
WHERE name LIKE '#%';
			

In SQL Server, temporary tables are created in the tempdb database, and their names always begin with a #. Local temporary tables start with a single #, while global temporary tables use ##. Using this query makes it easy to identify all active temporary tables, which is especially useful for debugging or monitoring session-based processes.

Find tables with a specific column

To list tables with a column name in SQL Server, you can query the INFORMATION_SCHEMA.COLUMNS view and filter by the column name:

SELECT TABLE_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE COLUMN_NAME = 'YourColumn';
			

This query returns all tables in the current database that contain the specified column. It is especially helpful when working with large or legacy databases where the same column might appear in multiple tables. Using INFORMATION_SCHEMA.COLUMNS is a reliable way to quickly locate where a column is used and understand how data is structured across different tables.

List tables with row count

To list tables with row count in SQL Server, you can query the sys.objects catalog view together with the dynamic management view sys.partitions:

SELECT o.name,  
       SUM(p.rows) AS row_count  
FROM sys.objects o  
JOIN sys.partitions p ON o.object_id = p.object_id  
WHERE o.type = 'U'  
GROUP BY o.name;
			

This query returns each user-defined table along with the number of rows it contains. The sys.partitions view provides up-to-date row counts, making it a practical solution for monitoring table sizes or identifying large tables in a database. While it is not a replacement for detailed performance analysis, it offers a quick way to assess data distribution across your tables.

Show all tables in a SQL Server database with dbForge Studio

Database specialists often use T-SQL commands as their primary option. An alternative approach is to utilize GUI tools like Microsoft's SQL Server Management Studio (SSMS) or dbForge Studio for SQL Server, which we used while preparing this material. These GUI tools offer a more user-friendly interface and allow users to perform actions with a simple click instead of writing scripts.

To view tables in dbForge Studio for SQL Server, you can use Database Explorer. Follow these steps:

1. Select the desired database in the left pane.
2. Expand the selected database to reveal its contents.
3. Expand the Tables folder to display all tables within the database. You can explore any specific table further to access information about its columns, triggers, indexes, constraints, and more.

Database Explorer not only enables you to view table details but also provides the ability to edit tables directly. To do this, simply right-click the desired table and choose the appropriate option from the menu.

Comparison table: Methods to list tables in SQL Server

Conclusion

Both T-SQL commands and GUI tools are widely used by database experts. GUI tools are particularly beneficial for beginners and non-technical users, but they are also favored by database experts due to their ability to accelerate tasks and reduce effort. For example, when working with databases, you can use GUI tools to show all tables in MySQL, PostgreSQL, or Oracle quickly and efficiently. In practice, you will most likely see the combination of T-SQL and GUI used by the same specialists when appropriate.

dbForge Studio for SQL Server stands out as an all-in-one solution for performing various tasks related to managing, designing, developing, and administering databases in SQL Server. It offers comprehensive functionality and can be experienced to its fullest extent through a fully functional free trial. Feel free to download the tool and give it a try.

FAQ