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.

8 comments
datanesia 15 Mar 2013 at 3:44 am
Vinod Salunke 03 Jan 2013 at 1:24 pm

this works for me..thanks

Akil 16 Jan 2013 at 6:11 pm

USE YourDBName
GO
SELECT *
FROM sys.Tables
GO

Derek Dieter 17 Jan 2013 at 6:19 pm

Thanks Akil, I’m not sure if that was available before 2008.

virender singal 03 Nov 2011 at 11:56 am

how see all tables of database in sql server 2005

Mac 26 Oct 2011 at 2:20 pm

Your last method with row count did not work for me. Your query over estimated the row count. True count is 78,120 and your query stated 312,480.

Derek Dieter 26 Oct 2011 at 3:06 pm

Hey Mac,

The fact that the number is wrong, most likely means your stats are out of wack.

http://sqlserverplanet.com/query-optimizer/using-dbcc-updateusage/

JP 18 Nov 2011 at 3:07 pm

Hi Mac,

Try changing SUM(sp.rows) to MAX(sp.rows).

This seems to be working for me so far…

Featured Articles

 Site Author