Microsoft KB Archive/176426

= INF: How to Determine the Number of Rows of Every Table in a Database =

Article ID: 176426

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q176426



SUMMARY
The following script will return the name and the number of rows in every user-defined table in a given database:

USE pubs -- replace pubs with your database name

SET NOCOUNT ON  DECLARE tables_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U'  OPEN tables_cursor DECLARE @tablename varchar(30), @quote char(1) SELECT @quote = ""

FETCH NEXT FROM tables_cursor INTO @tablename WHILE (@@fetch_status <> -1) BEGIN EXEC ('Select ' + @quote+'Rows in ' + @tablename + ' = '+                 @quote + ', count(*) from '+  @tablename) FETCH NEXT FROM tables_cursor INTO @tablename END DEALLOCATE tables_cursor SET NOCOUNT OFF



MORE INFORMATION
Notice that the "select count(*)" statement on a large table can be time consuming. Also, for additional information on every table, see the command DBCC CHECKDB in SQL Server Books Online.

Additional query words: records

Keywords: kbhowto kbprogramming KB176426

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.