Sử dụng COUNT .

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

*/

---------- 

Source : sqlOUNT