SQL 2005 - Query to Find Tables with Most Rows

Question

I searched for this for a while but came up empty ... hopefully someone here can help.

Is there a query I can run on a database (SQL Server 2005) that will return the number of rows in each table?

Answer 1

You could try something like this:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

Answer 2

this will return row_count, col_count, data_size

enjoy!.

http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/

Answer 3

Galwegian got it almost right :-) For SQL Server 2005 and up, I always recommed using the "sys.*" system views instead of the (soon to be deprecated) sysobjects and sysindexes tables.

SELECT 
    t.NAME AS 'Table Name',
    SUM(p.[Rows]) as 'Row Count'
FROM 
    sys.tables t
INNER JOIN   
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND  
    i.index_id = 1
GROUP BY 
    t.NAME 
ORDER BY 
    SUM(p.[Rows]) DESC

Marc

Answer 4

I don't remember where I got this, I'm pretty sure I didn't write it and it's definitely overkill but it's still fun.

USE master
GO

SET NOCOUNT ON

DECLARE @EnumDBs TABLE ([id] INT IDENTITY, [sql] VARCHAR(1024), PRIMARY KEY ([id]))
CREATE TABLE #EnumTables ([id] INT IDENTITY, [db] SYSNAME, [owner] SYSNAME, [table] SYSNAME, PRIMARY KEY ([db], [owner], [table], [id]))
CREATE TABLE #TableMetrics ([id] INT IDENTITY, [db] SYSNAME NULL, [table] SYSNAME, [rows] INT, [reserved] VARCHAR(256), [data] VARCHAR(256), [index_size] VARCHAR(256), [unused] VARCHAR(256), PRIMARY KEY ([table], [id]))

DECLARE @i INT
DECLARE @sql VARCHAR(1024)
DECLARE @db SYSNAME

INSERT INTO @EnumDBs
SELECT '    USE ['+name+']
     DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS
     INSERT INTO #EnumTables
     SELECT '''+name+''' AS [db]
        , u.name AS [owner]
        , o.name AS [table]
     FROM sysobjects AS o
     JOIN sysusers AS u
     ON o.uid = u.uid
     WHERE type = ''U''
     ORDER BY  u.name
        , o.name' AS [sql]
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE dbid > 4

SELECT @i=COUNT(*)
    , @sql=''
FROM @EnumDBs
WHILE @i > 0
BEGIN

    SELECT @sql = SQL
    FROM @EnumDBs
    WHERE ID = @i

    IF @@ROWCOUNT > 0
     EXEC(@sql)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    SET @i=@i-1
END

SELECT @i=COUNT(*)
    , @sql=''
FROM #EnumTables

WHILE @i > 0
BEGIN

    SELECT @db=[db]
     , @sql='USE ['+[db]+'] EXEC SP_SPACEUSED ''['+[db]+'].['+[owner]+'].['+[table]+']'''
    FROM #EnumTables
    WHERE ID = @i

    --PRINT @SQL
    INSERT INTO #TableMetrics
    ([table], [rows], [reserved], [data], [index_size], [unused])
    EXEC(@sql)
    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    UPDATE #TableMetrics
     SET [db]=@db
    WHERE [db] IS NULL
    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    SET @i=@i-1
END

SELECT * FROM #TableMetrics ORDER BY [db], CAST(REPLACE([reserved],' KB','') AS INT) DESC

ERR_HANDLER:
DROP TABLE #EnumTables
DROP TABLE #TableMetrics

Answer 5

here's my version which uses a dm_db_partition_stats DMV:

SELECT object_name(object_id) as table_name,
    SUM (row_count) AS total_number_of_rows 
FROM sys.dm_db_partition_stats
WHERE   (index_id=0 or index_id=1)
group by object_name(object_id)
order by SUM (row_count) desc

Answer 6

try this:

create table:

create table maxRows (tablename varchar(900) primary key,CountOf int)

run this and copy the output:

EXEC sp_msforeachtable 'print ''INSERT INTO maxRows SELECT ''''?'''', COUNT(*) FROM ? WITH (NOLOCK)'''

run the copied output, it inserts into the table a row count for each table

now run this, lists the tables with their row count:

select countOf,tablename from maxRows order by CountOf DESC

Answer 7

When run in a database, the following returns table row counts and index/total space usage.

declare @tableName  varchar(100);

create table
    #table
    (
        tablename   sysname,
        row_count   int,
        reserved    varchar(50),
        data        varchar(50),
        index_size  varchar(50),
        unused      varchar(50)
    );

create table
    #table2
    (
        tableName   varchar(100),
        row_count   int,
        data_size   int,
        index_size  int,
        reserved    int,
        unused      int
    );

declare C_T cursor fast_forward for
    select      TableName = '[' + s.name + '].[' + t.name + ']'
    from        sys.tables t
                inner join sys.schemas s on t.schema_id = s.schema_id
    where       t.type = 'U'
    order by    s.name,
                t.name;

open    C_T;

fetch next from C_T into @tableName;

while   @@FETCH_STATUS = 0
    begin
        truncate table  #table;

        insert into #table
        exec        sp_spaceused @tableName, false;

        insert into #table2(tableName, row_count, data_size, index_size, reserved, unused)
        select      @tableName,
                    row_count,
                    convert(int, replace(data, ' KB', '')),
                    convert(int, replace(index_size, ' KB', '')),
                    convert(int, replace(reserved, ' KB', '')),
                    convert(int, replace(unused, ' KB', ''))
        from        #table;

        fetch next from C_T into @tableName;
    end

close       C_T;
deallocate  C_T;

select      [Table Name] = tableName,
            [Rows] = row_count,
            [Data Size (KB)] = data_size,
            [Indexes Size (KB)] = index_size,
            [Reserved Space (KB)] = reserved,
            [Unused Space (KB)] = unused
from        #table2
order by    data_size desc;

drop table      #table;
drop table      #table2;