SCRIPT: Table Row Count - SQL Server Forums

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources