How to List Tables in SQL Server (T-SQL and SSMS)

To list user tables in SQL Server, query the sys.tables catalog view. Join it to sys.schemas to show each table’s schema, since table names can repeat in different schemas. These views report the current database; metadata visibility can also hide objects that your account can’t access.

List tables in the current database

SELECT
    s.name AS schema_name,
    t.name AS table_name
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
ORDER BY s.name, t.name;

sys.tables returns user tables, while sys.schemas supplies their schema names. The query runs in the database selected for the connection:

SELECT DB_NAME() AS current_database;

To list tables in a different database on a SQL Server instance, change the connection’s database or, where supported, run USE database_name before the query. In Azure SQL Database, connect directly to the target database; USE can’t switch to another database.

List tables in one schema

Add a schema filter to narrow the results, for example to the Sales schema:

SELECT t.name AS table_name
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE s.name = N'Sales'
ORDER BY t.name;

Replace Sales with the schema name you want to inspect. Use the fully qualified name, such as Sales.OrderHeader, when querying a table whose schema isn’t dbo.

Use INFORMATION_SCHEMA.TABLES

For a SQL-standard metadata view, query INFORMATION_SCHEMA.TABLES and filter to base tables so views aren’t included:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

This view returns tables and views in the current database that the current user can access. Microsoft notes that information schema views can be incomplete for newer SQL Server features; use catalog views such as sys.tables when you need SQL Server-specific metadata.

List tables in SQL Server Management Studio

In Object Explorer, connect to the server, expand Databases, expand the database you want, then expand Tables. This shows tables visible to your account. Refresh the database node if you created a table in another query window after Object Explorer was opened.

Troubleshoot a missing table

First check the database context and schema:

SELECT DB_NAME() AS current_database, SCHEMA_NAME() AS default_schema;

Then rerun the sys.tables query without a schema filter. If the table still doesn’t appear, check that the connected account has permission on it. SQL Server limits catalog metadata visibility to objects the current principal owns or can access; a missing row doesn’t prove that the table doesn’t exist.

For the official catalog view details, see sys.tables and sys.schemas. For the alternative metadata view, see INFORMATION_SCHEMA.TABLES. Microsoft documents the database switch behavior in USE.

To compare table-listing commands across database systems, see how to list tables in SQL databases.

After finding a table, see how to list its columns across databases.