-
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
-
GETTING ROW COUNTS FROM DATABASE (reply)
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
-
GETTING ROW COUNTS FROM DATABASE (reply)
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 = 'U'
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) + ' ' + 'KB',
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB',
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB',
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB'
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
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
-
GETTING ROW COUNTS FROM DATABASE (reply)
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 = 'U'
open test_cursor
fetch next from test_cursor
into @Tab_name
While @@fetch_status = 0
Begin
Print @Tab_Name
Exec('Select count(*) from '+@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'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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|