Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 SCRIPT: Table Row Count

Author  Topic 

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-03-05 : 08:58:45
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
   

- Advertisement -