How to List Tables in SQL Server

How do I list all tables in a SQL Server database?

Returns names and metadata for every table in the current SQL Server database.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the fastest way to list tables in SQL Server?

Query the catalog view sys.tables. It is lightweight, indexed, and always up-to-date, making it the go-to option for developers and scripts.

How do I list tables with their schemas?

Join sys.tables with sys.schemas or filter on INFORMATION_SCHEMA.TABLES. This adds the schema name to every table, avoiding naming collisions.

Why use INFORMATION_SCHEMA instead of sys.tables?

INFORMATION_SCHEMA.TABLES is ANSI-standard, so code ports easily to other RDBMS. However, it omits SQL Server–specific metadata.

How can I filter tables by name pattern?

Apply a WHERE clause with LIKE against t.name or TABLE_NAME. Use wildcards (e.g., 'Orders%') to narrow large catalogs quickly.

Can I see row counts while listing tables?

Join sys.dm_db_partition_stats to sys.tables and aggregate row_count. This delivers live counts without running separate queries.

Best practices for production?

Select only columns you need, limit to user schemas (schema_id >= 4), and add conditions to avoid scanning unnecessary system objects.

Why How to List Tables in SQL Server is important

How to List Tables in SQL Server Example Usage


-- List all ecommerce tables in dbo schema
SELECT s.name AS schema_name,
       t.name AS table_name,
       t.create_date
FROM   sys.tables  AS t
JOIN   sys.schemas AS s ON s.schema_id = t.schema_id
WHERE  s.name = 'dbo'
  AND  t.name IN ('Customers', 'Orders', 'Products', 'OrderItems');

How to List Tables in SQL Server Syntax


-- Option 1: Catalog view (recommended)
SELECT s.name AS schema_name,
       t.name AS table_name,
       t.create_date,
       t.modify_date
FROM   sys.tables AS t
JOIN   sys.schemas AS s ON s.schema_id = t.schema_id;

-- Option 2: ANSI standard view
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE';

-- Option 3: Include row counts
SELECT s.name  AS schema_name,
       t.name  AS table_name,
       SUM(p.rows) AS total_rows
FROM   sys.tables AS t
JOIN   sys.schemas AS s ON s.schema_id = t.schema_id
JOIN   sys.dm_db_partition_stats AS p
       ON p.object_id = t.object_id AND p.index_id IN (0,1)
GROUP  BY s.name, t.name
ORDER  BY total_rows DESC;

Common Mistakes

Using the GUI exclusively. SSMS Object Explorer is slow on large catalogs; a simple SELECT on sys.tables is faster and scriptable.
Querying INFORMATION_SCHEMA without filtering TABLE_TYPE, which returns views as well. Add WHERE TABLE_TYPE = 'BASE TABLE' to avoid confusion.