Quickly Retrieving the Row Count for All Tables in a SQL Database

By | March 23, 2012

A quick post that proved helpful to me today. I needed to see the number of rows for all tables in the database. I found this query:

SELECT ‘[‘ + SCHEMA_NAME(t.schema_id) + ‘].[‘ + t.name + ‘]’ AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2

on Brian Knights blog: Quickly Retrieving the Row Count for All Tables. One thing to note is that the numbers could be a little out of date. DBCC UPDATEUSAGE (DBNAME) should resolve that, the problem is that this only works when you are admin. If you want to be 100% accurate you might need to do a little more investigation.

  • Danny

    Thanks Kasper.

    Another approach will be querying the sys.objects and sys.partitions tables:

    SELECT O.Name AS TableName, P.rows AS NumberOfRows
    FROM sys.objects O

    INNER JOIN sys.partitions P ON (O.object_id = P.object_id)

    WHERE O.type = ‘U’

  • We use a view for this. Can be very handy;

    SELECT TOP 100 PERCENT O.name AS TableName,
    s.name AS SchemaName,
    I.rowcnt AS NrOfRecords
    FROM sysobjects AS O
    INNER JOIN sysindexes AS I ON O.id = I.id
    INNER JOIN sys.schemas AS S ON O.uid = S.schema_id
    WHERE I.indid IN (0, 1)
    AND O.xtype = ‘u’
    AND O.[name] NOT IN (‘sysdiagrams’, ‘dtproperties’)
    ORDER BY O.[name]