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)
SELECT TABLE_NAME,
-1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
WHILE (SELECT COUNT(*) FROM @tables WHERE ROW_COUNT = -1) > 0
BEGIN
SELECT @table = TABLE_NAME
FROM @tables
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
UPDATE @tables
SET ROW_COUNT = @row_count
WHERE TABLE_NAME = @table
END
SELECT * FROM @tables
- Onamuji
SCRIPT: Table Row Count
Posted - 03/05/2002 : 08:58:45
