SQL Server 查询表的记录数(3种方法，推荐第一种)

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