DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to count quickly the rows in all tables?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to list accurate and approximate (fast) rowcounts for all the tables in the database.

-- ACCURATE method - it takes longer - QUICK SYNTAX

-- SQL Server count all rows in all tables - sql server rowcount all tables

DECLARE  @CountStats  TABLE(SchemaName SYSNAME,

                            TableName  SYSNAME,

                            RowsCount  INT )

INSERT @CountStats

EXEC sp_msForEachTable

  'SELECT PARSENAME(''?'', 2), PARSENAME(''?'', 1), COUNT(*) FROM ?'

 

SELECT   *

FROM     @CountStats

ORDER BY RowsCount DESC

 

/* Partial results

 

SchemaName  TableName                     RowsCount

Sales       SalesOrderDetail              121317

Production  TransactionHistory            113443

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

*/
----------

-- APPROXIMATE - very quick

-- SQL count rows in all tables

USE AdventureWorks2008;

 

DECLARE @SQL NVARCHAR(255)

    SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'

    EXEC sp_executeSQL @SQL

 

SELECT Schema_name(t.schema_id) AS SchemaName,

       t.name                   AS TableName,

       i.rows                   as [Rows]

FROM   sys.tables AS t

       INNER JOIN sys.sysindexes AS i

         ON t.object_id = i.id

            AND i.indid < 2

ORDER BY [Rows] DESC

GO

/* Partial results

SchemaName  TableName                     Rows

Sales       SalesOrderDetail              121317

Production  TransactionHistory            113443

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

Production  WorkOrderRouting              67131

*/
----------

-- SQL Server 2000 - approximate quick count

USE Northwind;

SELECT      TableName = o.name,

            [Rows] = replace(convert(VARCHAR,convert(MONEY,max(i.rows)),1), '.00','')

FROM sysobjects o

      INNER JOIN sysindexes i

            ON o.id = i.id

WHERE xtype = 'u'

            AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1

GROUP BY o.name

ORDER BY max(i.rows) DESC

GO

/* Partial resutls

 

TableName               Rows

Order Details           2,155

Orders                  830

Customers               91

Products                77

Territories             53

EmployeeTerritories     49 */
----------

-- ACCURATE COUNT with cursor

-- SQL Server find rows count in all tables and views - select count(*)

USE AdventureWorks2008;

DECLARE  @SchemaName SYSNAME,

         @TableName  SYSNAME,

         @TableType  varchar(12)

 

DECLARE  @SQL NVARCHAR(MAX)

 

CREATE TABLE #Population (

  TableName  VARCHAR(256),

  TableType varchar(12),

  [Population] INT);

 

 

DECLARE curTablesAndViews CURSOR FAST_FORWARD FOR

      SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

      FROM   INFORMATION_SCHEMA.TABLES

 

OPEN curTablesAndViews

FETCH NEXT FROM curTablesAndViews

INTO @SchemaName,

     @TableName,

     @TableType

 

WHILE (@@FETCH_STATUS = 0)

  BEGIN

    SELECT @SQL = 'INSERT #Population SELECT ''' +

    @SchemaName + '.' + @TableName +

    ''','''+ @TableType+''', COUNT(*) as Population ' +

    'FROM [' + @SchemaName + '].[' + @TableName + ']'

    PRINT @SQL -- debugging

    EXEC SP_EXECUTESQL   @SQL

    

    FETCH NEXT FROM curTablesAndViews

    INTO @SchemaName,

         @TableName,

         @TableType

  END

 

CLOSE curTablesAndViews

DEALLOCATE curTablesAndViews

 

-- Return the list of rows counts

SELECT   *

FROM     #Population

ORDER BY [Population] DESC

GO

 

DROP TABLE #Population

/* 

TableName                     TableType   Population

.......

Person.BusinessEntityAddress  BASE TABLE  19614

Person.Address                BASE TABLE  19614

Sales.CreditCard              BASE TABLE  19118

Sales.PersonCreditCard        BASE TABLE  19118

Sales.vIndividualCustomer     VIEW        18508

.......

*/
----------

-- SQL Server 2005/2008 - approximate, very quick

DECLARE @SQL NVARCHAR(255)

SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'

EXEC sp_executeSQL @SQL

 

SELECT schema_name(o.schema_id)    AS SchemaName,

       object_name(o.object_id)    AS TableName,

       SUM(row_count)              AS [Count]

FROM     sys.dm_db_partition_stats ps

         INNER JOIN sys.objects o

           ON o.object_id = ps.object_id

WHERE    index_id < 2

         AND TYPE = 'U'

GROUP BY o.schema_id,  o.object_id

ORDER BY [Count] desc

/*

SchemaName  TableName                     Count

Production  TransactionHistory            14520704

Sales       SalesOrderDetail              121317

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

*/

---------- 

 

Related articles:

 

How do I get a list of SQL Server tables and their row counts?

 

Counting rows for all tables at once

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE