List All Tables in a Database

List All Tables in a Database

There are a few methods for listing all the tables in a database. Some methods will be able to show more specifics than others due to the capabilities inherent in the tables being joined. I’ll show you the simplest way first which is probably the most supported way.

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

This method makes use of documented INFORMATION_SCHEMA system view.

The next method makes use of two not so well documented system views.

SELECT
    FullName    = s.name + '.' + t.name
    ,SchemaName = s.name
    ,TableName  = t.name
FROM sys.TABLES t
JOIN sys.schemas s
ON s.schema_id = t.schema_id
ORDER BY s.name, t.name

There is really no difference between these two methods.

The third method uses the sp_tables stored procedure and passes in the parameter of ‘TABLE’. Though the parameter being passes looks funny, this is how to make it work.

EXEC sp_tables @table_type = "'TABLE'"

Show all Tables with Rowcount and Dataspace

This method is the most extensive. Not only will it show you all the tables, but it will also display the rowcount and datasize in Megabytes.

SELECT *
FROM (
    SELECT
        TableName   = t.TABLE_SCHEMA + '.' + t.TABLE_NAME
        ,[RowCount] = SUM(sp.[ROWS])
        ,Megabytes  = (8 * SUM(CASE WHEN sau.TYPE != 1 THEN sau.used_pages WHEN sp.index_id < 2 THEN sau.data_pages ELSE 0 END)) / 1024
    FROM INFORMATION_SCHEMA.TABLES t
    JOIN sys.partitions sp
        ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
    JOIN sys.allocation_units sau
        ON sau.container_id = sp.partition_id
    WHERE TABLE_TYPE = 'BASE TABLE'
    GROUP BY
        t.TABLE_SCHEMA + '.' + t.TABLE_NAME
) A
ORDER BY TableName

This method uses the INFORMATION_SCHEMA system view and joins agains the sys.partitions view in order to get the counts. The megabytes calculation multiples the pages by the page size, then divides by 1024 in order to get the megabytes calculation. I took a piece of code that was executed by SSMS in order to generate this query.