none
counting records in all tables

    Question

  • Hello all,

       I was wondering if there is anyway to find number of records in all tables instead of count one table at a time by select count(*) from table_name.

    thanks

     

    • Moved by Tom PhillipsModerator Wednesday, July 07, 2010 3:39 PM TSQL Question (From:SQL Server Database Engine)
    Friday, February 16, 2007 4:13 PM

Answers

  • The system tables are good for this.  They're also faster than COUNT(*) for single tables, since SQL Server doesn't currently optimize that query.

    I use the following to get the row count, data bytes, and index bytes for all tables:

    SELECT

    sys.schemas.[name] AS [Schema],

    sys.tables.name AS [Table],

    COALESCE([Row Count].[Count], 0) AS [Rows],

    COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],

    COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]

    FROM sys.tables

    INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id

    LEFT OUTER JOIN (

    SELECT

    object_id,

    SUM(rows) AS [Count]

    FROM sys.partitions

    WHERE index_id < 2

    GROUP BY object_id

    ) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id

    LEFT OUTER JOIN (

    SELECT

    sys.indexes.object_id,

    SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

    FROM sys.indexes

    INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

    AND p.index_id = sys.indexes.index_id

    INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

    GROUP BY sys.indexes.object_id

    ) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id

    LEFT OUTER JOIN (

    SELECT

    sys.indexes.object_id,

    SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

    FROM sys.indexes

    INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

    AND p.index_id = sys.indexes.index_id

    INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

    GROUP BY sys.indexes.object_id

    ) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id

    ORDER BY sys.tables.[name]

    (The weird text editor on this site messed up the formatting of the code; I apologize)

    -Ryan

    Friday, February 16, 2007 4:53 PM
  • You are not guaranteed that the numbers you get back via this method will be correct. The best way to do it is to create a script that does a SELECT COUNT(*) FROM TABLE for all tables.

    Just wondering: why are you interested in this information?

    Thanks,

    Friday, February 16, 2007 8:05 PM
  • For an approximate row count of all user tables in a particular database in SQL Server 2000, use this:

    SELECT OBJECT_NAME(si.[id]), si.[rows]

    FROM dbo.sysindexes si

    WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

    AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

    AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

    ORDER BY 1

     

    If you want to include the tables' owners then you could use this:

    SELECT su.[name] AS [Owner Name], so.[name] AS [Object Name], si.[rows]

    FROM dbo.sysindexes si

    INNER JOIN dbo.sysobjects so ON so.[id] = si.[id]

    INNER JOIN dbo.sysusers su ON su.[uid] = so.[uid]

    WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

    AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

    AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

    ORDER BY 1

     

    If you want accurate row counts and can't be bothered creating and using a cursor then use this:

    CREATE TABLE #tmpOutput (TableName VARCHAR(300), TableRowCount INT)

    EXEC dbo.sp_MSForEachTable 'INSERT INTO #tmpOutput(TableName, TableRowCount) SELECT ''?'', COUNT(*) FROM ?'

    SELECT TableName, TableRowCount

    FROM #tmpOutput

    ORDER BY 1

    DROP TABLE #tmpOutput

     

    Chris

    Tuesday, February 20, 2007 8:02 PM
  • There are 2 scripts in the end of this blog solving this problem:

    How to get information about all databases without a loop


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, July 11, 2010 3:51 AM

All replies

  • The system tables are good for this.  They're also faster than COUNT(*) for single tables, since SQL Server doesn't currently optimize that query.

    I use the following to get the row count, data bytes, and index bytes for all tables:

    SELECT

    sys.schemas.[name] AS [Schema],

    sys.tables.name AS [Table],

    COALESCE([Row Count].[Count], 0) AS [Rows],

    COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],

    COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]

    FROM sys.tables

    INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id

    LEFT OUTER JOIN (

    SELECT

    object_id,

    SUM(rows) AS [Count]

    FROM sys.partitions

    WHERE index_id < 2

    GROUP BY object_id

    ) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id

    LEFT OUTER JOIN (

    SELECT

    sys.indexes.object_id,

    SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

    FROM sys.indexes

    INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

    AND p.index_id = sys.indexes.index_id

    INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

    GROUP BY sys.indexes.object_id

    ) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id

    LEFT OUTER JOIN (

    SELECT

    sys.indexes.object_id,

    SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

    FROM sys.indexes

    INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

    AND p.index_id = sys.indexes.index_id

    INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

    GROUP BY sys.indexes.object_id

    ) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id

    ORDER BY sys.tables.[name]

    (The weird text editor on this site messed up the formatting of the code; I apologize)

    -Ryan

    Friday, February 16, 2007 4:53 PM
  • Hello Ryan and all,

       I am using SQL Server 2000; therefore, I won't be able to see table as sys.schemas and sys.tables. Would you advise how to do this in SQL Server 2000 ?

    thanks

     

    Friday, February 16, 2007 6:24 PM
  • You are not guaranteed that the numbers you get back via this method will be correct. The best way to do it is to create a script that does a SELECT COUNT(*) FROM TABLE for all tables.

    Just wondering: why are you interested in this information?

    Thanks,

    Friday, February 16, 2007 8:05 PM
  • hello Marcel and all,

    I know there will be no guarantee unless using select(*). However, estimate number would be fine. Because I am looking for large table to tune.

    Friday, February 16, 2007 8:34 PM
  • Unfortunately, we're fully upgraded to SQL 2005 here (we were active in the beta and were ready to go when 2005 was released).  So I can't accurately convert it back to 2000 code.

    While SQL 2000 doesn't have a sys.tables, it does have a systables.  It should be possible to get this working on the older version with minor adjustments.

    -Ryan

    Monday, February 19, 2007 4:29 PM
  • This is a quick and dirty procedure that uses the results from sp_spaceused to create a simple report. I've used it to weed out a few space hogs on a 2000 server. Note that getting accurate row counts is probably contingent upon having your usage statistics up to date (see DBCC UPDATEUSAGE). Create the procedure in master, and run it from within the database you want to profile, like other sp_ procedures. Tweak it to suit your needs - I'm sure there's room for improvement.

    CREATE PROCEDURE sp_tablesizes @biggestfirst bit = 0
    AS
    -- sp_tablesizes
    -- Dave Britten, 2007
    -- Produces a list of all user and system tables in the current database,
    -- optionally sorted by physical size. Also returns grand totals in a
    -- second result set.

    SET NOCOUNT ON
    CREATE TABLE #tablelist (
        Owner sysname,
        Name sysname
    )

    CREATE TABLE #tabledata (
        Name varchar(128),
        Rows char(11),
        reserved varchar(18),
        Data varchar(18),
        index_size varchar(18),
        Unused varchar(18)
    )

    CREATE TABLE #sizedata (
        rows int,
        reserved int,
        Data int,
        index_size int,
        Unused int
    )

    DECLARE @dbname sysname
    SET @dbname = db_name()
    DECLARE @tablename sysname
    DECLARE @ownername sysname
    DECLARE @tablestring varchar(261)

    INSERT INTO #tablelist
    EXEC('SELECT sysusers.name, sysobjects.name FROM [' + @dbname + ']..sysusers INNER JOIN [' + @dbname + ']..sysobjects ON sysobjects.uid = sysusers.uid WHERE type IN (''U'', ''S'') AND sysobjects.name NOT LIKE ''tempdb..%''')

    DECLARE tables CURSOR FAST_FORWARD FOR
    SELECT Owner, Name FROM #tablelist
    OPEN tables

    FETCH NEXT FROM tables INTO @ownername, @tablename
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @tablestring = '[' + @ownername + '].[' + @tablename + ']'
        INSERT INTO #tabledata
        EXEC sp_spaceused @tablestring
        FETCH NEXT FROM tables INTO @ownername, @tablename
    END

    CLOSE tables
    DEALLOCATE tables

    INSERT INTO #sizedata (rows, reserved, data, index_size, unused)
    SELECT
        rows,
        CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int),
        CAST(LEFT(data, PATINDEX('% KB', data)) AS int),
        CAST(LEFT(index_size, PATINDEX('% KB', index_size)) AS int),
        CAST(LEFT(unused, PATINDEX('% KB', unused)) AS int)
    FROM #tabledata

    IF @biggestfirst = 1
        SELECT * FROM #tabledata ORDER BY CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int) DESC
    ELSE
        SELECT * FROM #tabledata ORDER BY name ASC

    SELECT
        SUM(rows) [Total Rows],
        STR(SUM(reserved)) + ' KB' [Total reserved],
        STR(SUM(data)) + ' KB' [Total Data],
        STR(SUM(index_size)) + ' KB' [Total index_size],
        STR(SUM(unused)) + ' KB' [Total Unused]
    FROM #sizedata

    DROP TABLE #tablelist
    DROP TABLE #tabledata
    DROP TABLE #sizedata

    GO

    Tuesday, February 20, 2007 5:20 PM
  • Hello davidbrit2 and all,

       Thank you davidbrit2 for the replied. When I complied the stored procedure, I got the problem at database name as a variable.

       e.g: DECLARE @dbName AS VARCHAR(30)

              SELECT @dbName = 'Another Database'

              SELECT name FROM [' + @dbName + ']..sysobjects WHERE type = 'U' ORDER BY name

     

    Then I received error:

    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name '' + @dbName + '..sysobjects'.

     

    Please advise.

    Tuesday, February 20, 2007 7:38 PM
  • For an approximate row count of all user tables in a particular database in SQL Server 2000, use this:

    SELECT OBJECT_NAME(si.[id]), si.[rows]

    FROM dbo.sysindexes si

    WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

    AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

    AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

    ORDER BY 1

     

    If you want to include the tables' owners then you could use this:

    SELECT su.[name] AS [Owner Name], so.[name] AS [Object Name], si.[rows]

    FROM dbo.sysindexes si

    INNER JOIN dbo.sysobjects so ON so.[id] = si.[id]

    INNER JOIN dbo.sysusers su ON su.[uid] = so.[uid]

    WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

    AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

    AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

    ORDER BY 1

     

    If you want accurate row counts and can't be bothered creating and using a cursor then use this:

    CREATE TABLE #tmpOutput (TableName VARCHAR(300), TableRowCount INT)

    EXEC dbo.sp_MSForEachTable 'INSERT INTO #tmpOutput(TableName, TableRowCount) SELECT ''?'', COUNT(*) FROM ?'

    SELECT TableName, TableRowCount

    FROM #tmpOutput

    ORDER BY 1

    DROP TABLE #tmpOutput

     

    Chris

    Tuesday, February 20, 2007 8:02 PM
  • Here is the script that can give you the number of counts in each table -

    Declare @testSql varchar(300)
    Create Table #TempTabCount(TABLE_QUALIFIER sysname NULL,
     TABLE_OWNER sysname NULL,
     TABLE_NAME sysname NULL,
     NON_UNIQUE smallint NULL,
     INDEX_QUALIFIER sysname NULL,
     INDEX_NAME sysname NULL,
     TYPE smallint NULL,
     SEQ_IN_INDEX smallint NULL,
     COLUMN_NAME sysname NULL,
     COLLATION char(1) NULL,
     CARDINALITY int NULL,
     PAGES int NULL,
     FILTER_CONDITION varchar(128) NULL)

    Declare TAB_CURSOR CURSOR for
    SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES

    Open TAB_CURSOR
    FETCH NEXT FROM TAB_CURSOR
    INTO @testSql
    WHILE @@FETCH_STATUS = 0
    BEGIN
     INSERT INTO #TempTabCount(TABLE_QUALIFIER,
     TABLE_OWNER,
     TABLE_NAME,
     NON_UNIQUE,
     INDEX_QUALIFIER,
     INDEX_NAME,
     TYPE,
     SEQ_IN_INDEX,
     COLUMN_NAME,
     COLLATION,
     CARDINALITY,
     PAGES,
     FILTER_CONDITION)
     exec sp_statistics @testSql
          FETCH NEXT FROM TAB_CURSOR INTO @testSql
    END
    CLOSE TAB_CURSOR
    DEALLOCATE TAB_CURSOR
    Select TABLE_OWNER, TABLE_NAME, CARDINALITY from #TempTabCount

    See if it solves your purpose. I tested it in SQL 2000 and working for me.

    Enjoy!

    Ash

    Tuesday, February 20, 2007 8:13 PM
  • Chris,

    Ah-ha, it's sysindexes. I knew they had to be storing that information SOMEWHERE. ;)

    Tuesday, February 20, 2007 9:33 PM
  • Hi All,

     

    Please find the query which will help to find the total number of records in all the tables in a SQL Server Database.

    Select substring(obj.name, 1, 50) as Table_Name,
    ind.rows as Number_of_Rows
    from sysobjects as obj inner join sysindexes as ind on obj.id = ind.id
    where
    obj.xtype = 'u'and ind.indid < 2
    order by
    obj.name

    Regards
    AKMEHTA

    Wednesday, March 07, 2007 12:45 PM
  • This is a very old topic but to make it sure for any one that needs a solution, the undocumented stored procedure sp_MSForEachTable can be used for this task.

    Here is an article on this topic  sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database

    EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'
    

    Above you can see how this sp can be used

    In the sql tutorial there is a method to save results in a database table and run select statements from this temp table,

    I hope that is helpful,

     

     


    SQL Server and T-SQL Tutorials
    Windows 7 Tips and Tricks
    Our true mentor in life is science
    Saturday, July 03, 2010 10:59 AM
  • Hi,

    You can try this...

    SELECT

     

    o.name AS "Table Name", i.rowcnt AS "Row Count"

    FROM

     

    sysobjects o, sysindexes i

    WHERE

     

    i.id = o.id

    AND

     

    indid IN(0,1)

    AND

     

    xtype = 'u'

    AND

     

    o.name <> 'sysdiagrams'

    ORDER

     

    BY i.rowcnt DESC

    COMPUTE

     

    SUM(i.rowcnt);

    GO


    Srikanth.kls
    Wednesday, July 07, 2010 9:15 AM
  • There are 2 scripts in the end of this blog solving this problem:

    How to get information about all databases without a loop


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, July 11, 2010 3:51 AM
  • The following page deals with the same topic presenting accurate and approximate methods:

    http://www.sqlusa.com/bestpractices2005/alltablesrowcount/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, July 16, 2010 8:11 PM