SELECT   a.name, b.rows

FROM      sysobjects AS a INNER JOIN

                 sysindexes AS b ON a.id = b.id

WHERE   (a.type = 'u') AND (b.indid IN (0, 1))

ORDER BY b.rows DESC

SELECT object_name (i.id) TableName,

       rows as RowCnt

FROM sysindexes i

INNER JOIN sysObjects o

    ON (o.id = i.id AND o.xType = 'U ')

WHERE indid < 2

ORDER BY TableName 

--****************** 

--two: 使用未公开的过程 "sp_MSforeachtable "

CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT)

EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?'

SELECT TableName, RowCnt FROM #temp ORDER BY TableName

DROP TABLE #temp 

--****************** 

-- three: 使用游标.cursor

SET NOCOUNT ON

DECLARE @tableName VARCHAR (255),

        @sql VARCHAR (300)

CREATE TABLE #temp (TableName VARCHAR (255), rowCnt INT)

DECLARE myCursor CURSOR FAST_FORWARD READ_ONLY FOR

    SELECT TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = 'base table '

OPEN myCursor

FETCH NEXT FROM myCursor INTO @tableName

WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC ( 'INSERT INTO #temp (TableName, rowCnt) SELECT ''' + @tableName + ''' as tableName, count(*) as rowCnt from ' + @tableName)

    FETCH NEXT FROM myCursor INTO @tableName

    END

SELECT TableName, RowCnt FROM #temp ORDER BY TableName

CLOSE myCursor

DEALLOCATE myCursor

DROP TABLE #temp