GETTING ROW COUNTS FROM DATABASE

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

Dianne,

I had a script somewhere, but can't seem to find it (changed jobs recently). Anyway, here's a "quick and dirty" 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 = "u"
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

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;),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39;),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39;),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39;)

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

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