Categories
Programming

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!)

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