SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 SCRIPT: Table Row Count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 03/05/2002 :  08:58:45  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000