http://blog.csdn.net/smahorse/article/details/8156483
--SQL Server 查询表的记录数   
  
--one: 使用系统表.    
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