Count of all tables/rows in a database

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

SQL Server 2000 Forums

SQL Server Development (2000)

Count of all tables/rows in a database

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-02 : 07:34:04

Joseph writes "I need to get the number of rows in each table in my database. There are thousands of tables so it's not an easy one by one process. Plus I have multiple databases. The trouble I'm having is not being able to use a variable name for the table name in an SQL select statement other than by using and Execute. Is there an easier way? I've included my code below. Any help would be very much appreciated!

We are on Windows 2000 and SQL 2000.

Thank you

declare @i int
set @i = 0
declare ibuffer cursor fast_forward for
select cast (name as varchar(30)) as name from sysobjects where xtype='U'
open ibuffer
declare @namex sysname
fetch next from ibuffer into @namex
while (@@fetch_status != -1)
begin
execute ('select count(*) from ' + @namex)
set @i = @i + 1
print cast (@i as varchar(4)) + ' ' + @namex
fetch next from ibuffer into @namex
end
deallocate ibuffer
print 'Finished: tbls = ' + cast (@i as varchar(4))
GO"

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-10-02 : 07:42:26

You should be able to modify this script to do it. As written it will only support a single database though.

http://www.sqlteam.com/item.asp?ItemID=282

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

dsdeming

479 Posts

Posted - 2003-10-02 : 08:33:59

Try this:

SELECT DISTINCT
TableName = o.name,
Rows = i.rows
FROM sysobjects o
JOIN sysindexes i ON o.id = i.id
WHERE o.xtype = 'U'
AND i.indid < 2

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources