Report the number of records in all tables in a database

[philrm]

Can anyone tell me how to script or where i can find a script to report on howmany records are in each table of a Microsift SQL 2000 database. I'm sure this is fairly easy but can't seem to find it.

Thanks in advance

 

[pankajv]

Try this:

DECLARE @Tab_Name As Varchar(100)
DECLARE @strSQL As Varchar(8000)
SET @strSQL = ''

DECLARE CUR_TAB CURSOR LOCAL
FOR SELECT Name
FROM sysobjects
where type = 'U'

OPEN CUR_TAB

FETCH NEXT FROM CUR_TAB
INTO @Tab_Name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = @strSQL + 'SELECT ''' + @Tab_Name + ''', COUNT(*) FROM ' + @Tab_Name + CHAR(13) + 'UNION' + CHAR(13)

FETCH NEXT FROM CUR_TAB
INTO @Tab_Name
END

CLOSE CUR_TAB
DEALLOCATE CUR_TAB

EXECUTE (@strSQL)

/* if the len of @strSQL exceeds 8000 characters then you may get errors */

 

[tlbroadbent]

It is much simpler that the method proposed by pankajv. See
thread183-487784 for two ways to get the row count. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm