How do I get list of all tables in a database using TSQL?

Learn how do i get list of all tables in a database using tsql? with practical examples, diagrams, and best practices. Covers sql-server, t-sql, database-table development techniques with visual ex...
Tags:
Categories:

How to List All Tables in a SQL Server Database

Database schema illustration with tables and relationships

Discover various T-SQL methods to retrieve a comprehensive list of all tables within a SQL Server database, including system tables, user tables, and temporary tables.

When working with SQL Server, it's a common requirement to list all tables present in a database. This can be useful for database administration, schema analysis, scripting, or simply understanding the database structure. T-SQL provides several ways to achieve this, each with its own advantages and use cases. This article will explore the most common and effective methods, ranging from standard information schema views to system catalog views.

Using INFORMATION_SCHEMA.TABLES

The INFORMATION_SCHEMA views provide a standard, SQL-92 compliant way to access metadata about a database. The INFORMATION_SCHEMA.TABLES view is particularly useful for listing tables. It contains a row for each table or view accessible to the current user within the current database. This method is generally preferred for its portability across different SQL database systems that support the SQL-92 standard.

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

Listing user-defined tables using INFORMATION_SCHEMA.TABLES

Using System Catalog Views (sys.tables)

For SQL Server-specific metadata, system catalog views are the most comprehensive and recommended approach. They provide more detailed information than INFORMATION_SCHEMA views and are often more performant. The sys.tables catalog view returns a row for each user-defined table in the database. This is the most robust method for SQL Server environments.

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    t.object_id AS ObjectId,
    t.create_date AS CreateDate
FROM
    sys.tables AS t
INNER JOIN
    sys.schemas AS s ON t.schema_id = s.schema_id
WHERE
    t.is_ms_shipped = 0 -- Exclude system tables
ORDER BY
    SchemaName, TableName;

Listing user-defined tables using sys.tables and sys.schemas

flowchart TD
    A[Start Query] --> B{"Select from sys.tables"}
    B --> C{"Join with sys.schemas"}
    C --> D{"Filter: is_ms_shipped = 0"}
    D --> E[Order Results]
    E --> F[Display Table List]

Flowchart of retrieving user tables using sys.tables

Listing All Objects (Tables, Views, System Tables)

Sometimes, you might need a broader list that includes not just user tables but also views, system tables, and other database objects. The sys.objects catalog view is perfect for this. It returns a row for every object in the database, including tables, views, stored procedures, functions, and more. You can filter by type to get specific object types.

SELECT
    s.name AS SchemaName,
    o.name AS ObjectName,
    o.type_desc AS ObjectType,
    o.create_date AS CreateDate
FROM
    sys.objects AS o
INNER JOIN
    sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
    o.type IN ('U', 'V', 'S') -- 'U' = User Table, 'V' = View, 'S' = System Table
ORDER BY
    SchemaName, ObjectName;

Listing user tables, views, and system tables using sys.objects