Steve Fenton

Getting a list of tables and row counts in SQL Server

I found myself needing to grab a list of all tables and their associated row counts from a SQL Server database. This can be done using a pretty simply query, but I suspect that sharing the query will save somebody some time in the future (possibly me).

So here is a query that you run against any database to get all of the tables and the count of rows (remember, if you are running this in SQL Management Studio and get strange looking results – you probably have “Master” selected!)

    SCHEMA_NAME(ST.schema_id) AS [Schema], AS [Name],
    '[' + SCHEMA_NAME(ST.schema_id) + '].[' + + ']' AS [FullName],
    SI.rows AS [RowCount]
    sys.tables AS ST
    sys.sysindexes AS SI
    ON ST.object_id = AND SI.indid < 2

Written by Steve Fenton on