# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  GETTING ROW COUNTS FROM DATABASE

## DIANNE

I need a procedure that will go to a database and give me all the row counts for the user tables.

Does anyone know how I can get this?

Thanks,
Dianne

----------


## Larry

Dianne,

I had a script somewhere, but can&#39;t seem to find it (changed jobs recently).  Anyway, here&#39;s a &#34;quick and dirty&#34; query that seems to work, but of course there are probably other (better) ways to get it done. Remember MS says to never query system tables directly, but....

  select distinct SUBSTRING(so.name,1,30) AS name , si.rows
  from sysindexes si, sysobjects so
  where si.id = so.id
  and si.name = so.name
  and so.type = &#34;u&#34;
  and so.status > 0

Hope this helps.
- Larry


------------
DIANNE at 11/15/00 1:35:20 PM


I need a procedure that will go to a database and give me all the row counts for the user tables.

Does anyone know how I can get this?

Thanks,
Dianne

----------


## Gary Andrews

Following is code got from someone else and modified to show all tables, instead
of the top 25 tables.  You need to modiy the USE statement prior to execution.

/**************************************************  ************************************
*
*  BigTables.sql
*  Bill Graziano (SQLTeam.com)
*  graz@sqlteam.com
*  v1.1
*
**************************************************  ************************************/
-- list rows in tables in a database g.andrews 8/8/00
--
use msdb  -- <--------------------modify as appropriate

declare @id	int			
declare @type	character(2) 		
declare	@pages	int			
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage	dec(15,0)
declare @pagesperMB		dec(15,0)

create table #spt_space
(
	objid		int null,
	rows		int null,
	reserved	dec(15) null,
	data		dec(15) null,
	indexp		dec(15) null,
	unused		dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select	id
from	sysobjects
where	xtype = &#39;U&#39;

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

	/* Code from sp_spaceused */
	insert into #spt_space (objid, reserved)
		select objid = @id, sum(reserved)
			from sysindexes
				where indid in (0, 1, 255)
					and id = @id

	select @pages = sum(dpages)
			from sysindexes
				where indid < 2
					and id = @id
	select @pages = @pages + isnull(sum(used), 0)
		from sysindexes
			where indid = 255
				and id = @id
	update #spt_space
		set data = @pages
	where objid = @id


	/* index: sum(used) where indid in (0, 1, 255) - data */
	update #spt_space
		set indexp = (select sum(used)
				from sysindexes
				where indid in (0, 1, 255)
				and id = @id)
			    - data
		where objid = @id

	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
	update #spt_space
		set unused = reserved
				- (select sum(used)
					from sysindexes
						where indid in (0, 1, 255)
						and id = @id)
		where objid = @id

	update #spt_space
		set rows = i.rows
			from sysindexes i
				where i.indid < 2
				and i.id = @id
				and objid = @id

	fetch next from c_tables
	into @id
end


select 
	Table_Name = (select left(name,25) from sysobjects where id = objid),
	rows = convert(char(11), rows),
	reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39 :Wink: ,
	data_KB = ltrim(str(data * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39 :Wink: ,
	index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39 :Wink: ,
	unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39 :Wink: 

from 	#spt_space, master.dbo.spt_values d
where 	d.number = 1
and 	d.type = &#39;E&#39;
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

HTHs  Gary
------------
DIANNE at 11/15/00 1:35:20 PM


I need a procedure that will go to a database and give me all the row counts for the user tables.

Does anyone know how I can get this?

Thanks,
Dianne

----------


## Venu

Hi Dianne,

Here is the stored procedure to count the rows

Create Procedure Count_rows
as
Declare @count numeric(8) 
Declare @Tab_name varchar(30)
Declare test_cursor Cursor for
Select Name from sysobjects where xtype = &#39;U&#39;
open test_cursor
fetch next from test_cursor
into @Tab_name
While @@fetch_status = 0 
Begin
	Print @Tab_Name
	Exec(&#39;Select count(*) from &#39;+@Tab_name)
	Fetch next from test_cursor 
	into @Tab_name
end
Close test_cursor
Deallocate test_cursor

Hope This helps
Venu
------------
Larry at 11/15/00 1:54:38 PM

Dianne,

I had a script somewhere, but can&#39;t seem to find it (changed jobs recently).  Anyway, here&#39;s a &#34;quick and dirty&#34; query that seems to work, but of course there are probably other (better) ways to get it done. Remember MS says to never query system tables directly, but....

  select distinct SUBSTRING(so.name,1,30) AS name , si.rows
  from sysindexes si, sysobjects so
  where si.id = so.id
  and si.name = so.name
  and so.type = &#34;u&#34;
  and so.status > 0

Hope this helps.
- Larry


------------
DIANNE at 11/15/00 1:35:20 PM


I need a procedure that will go to a database and give me all the row counts for the user tables.

Does anyone know how I can get this?

Thanks,
Dianne

----------

