How to List Tables in SQL Server

sys.tables covers the current database and needs the join to sys.schemas because it stores schema_id rather than the schema name. It excludes views, which is usually what you want. It also carries extras the standard views lack, such as create_date, modify_date, and is_ms_shipped for filtering out system objects.

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

Portable version

INFORMATION_SCHEMA.TABLES is standard SQL and behaves the same on other engines, which matters if the query is shared or lives in a tool targeting several databases. It mixes tables and views together, so the TABLE_TYPE filter is not optional. Microsoft's own guidance is to prefer the sys catalog views when you need accuracy about SQL Server specifics.

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