Prompted by a thread in another forum... I saw a cursor version of this script and decided to step it up a knotch to a simple loop. I'm sure a better version would be writing a UDF to fetch the row count in a table and then just write one insert or select statement to get the same results, which would be faster, who knows... Try ripping out the @tables and turning it into a temp table if you are on SQL 7...
SET NOCOUNT ON
DECLARE @tables TABLE (TABLE_NAME VARCHAR(256), ROW_COUNT BIGINT)
DECLARE @row_count BIGINT
DECLARE @table VARCHAR(256)
DECLARE @sql NVARCHAR(4000)
INSERT INTO @tables (TABLE_NAME, ROW_COUNT)
WHERE TABLE_TYPE = 'BASE TABLE'
WHILE (SELECT COUNT(*) FROM @tables WHERE ROW_COUNT = -1) > 0
SELECT @table = TABLE_NAME
WHERE ROW_COUNT = -1
SET @sql = 'SELECT @row_count = COUNT_BIG(*) FROM ' + @table
EXEC sp_executesql @sql, N'@row_count BIGINT OUTPUT', @row_count OUTPUT
SET ROW_COUNT = @row_count
WHERE TABLE_NAME = @table
SELECT * FROM @tables