October 7, 2004 at 3:17 am
How can I get the count of all rows in all tables? I need to be able to see how many rows there are in each table within my database.
October 7, 2004 at 5:50 am
You could reference sysobjects table (for table objects) and incorporate that with the ForEachDB (I have seen this posted here before don't remember exact syntax) and build dynamic SQL to provide the rowcounts.
OR you could look at sysindexes and this will provide approximate rowcounts.
You can research more in BOL for sysobjects and sysindexes
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 7, 2004 at 7:06 am
sp_MSforeachtable will help do what you are looking for.
Someone passed this on to me at this site. Search for it and you will see the code to do exactly what you want.
October 8, 2004 at 6:15 am
Try this:
exec sp_MSforeachtable "print '?' SELECT Count(*) FROM ? "
Another simple way is to run the following script, which generates all of your SELECT statements. Then, cut and paste the resulting code and run it.
SELECT 'SELECT Count(*) FROM ' + name
FROM sysobjects
WHERE type = 'U'
ORDER BY name
October 8, 2004 at 6:31 am
if you are worried about performance you can use this :
exec sp_MsForEachTable 'Select ''?'' as TableName, max(rowcnt) as Total from dbo.sysindexes where id = object_id(''?'') and indid < 2'
This query scan the sysindexes table and read the rowcount instead of doing a full index scan of the table (which takes a lot more time than to do if you have a lot of rows)
The downside of this method is that if you need an exact value and that the statistic of the table are not correct you can get a false reading (from a few rows from my experience).
October 8, 2004 at 7:42 am
I think I found this script on this web site:
--List Table Names and Sizes
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 TableName = (select left(name,60) from sysobjects where id = objid),
Rows = convert(char(11), rows),
ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
UnusedKB = 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
Michael Lee
October 8, 2004 at 10:28 am
Of course, the use of the data will decide the code you need to write. I put together some web pages to give this type of data to some of my users that need to know.
This is a portion of the code used on the backend to provide this data to them.
I added the temp table code here since the actual table names mean nothing.
set nocount on
declare @command varchar(250)
declare @table varchar(250)
declare @@noOfRows int
declare @q char(1)
set @q = char(39)
drop table ##rowCount
create table ##rowCount ( [tableName] varchar(250) NOT NULL ,
[rowsInTable] int NOT NULL default 0 )
drop table #tables
select [name]
into #tables
from sysobjects
where xtype = 'u'
while exists ( select * from #tables )
begin
select @table = ( select top 1 name from #tables )
delete from #tables where name = @table
insert into ##rowCount ( [tableName] ) values ( rtrim(@table) )
set @command = 'update ##rowCount set rowsInTable = (select count(*) from ['+rtrim(@table)+']) where tableName = '+@q+rtrim(@table)+@q
execute (@command)
end
select sum(rowsInTable) from ##rowCount
select rowsInTable , tableName from ##rowCount
November 3, 2005 at 2:03 pm
Quick question,
for the first exec sp_msforeachtable, what do you put in for the '?'
November 4, 2005 at 6:46 am
sp_MSforeachtable is un-documented so be aware of that. The behavior might change in future versions. And Jennifer, you do not need to put anything for the ?. That is just a placeholder and will iterate through the list of all the tables, print out the name of that table followed by the number of records in it.
Another SQL to quickly count the number of records is shown below - The results of this sql depends upon whether the statistics have been kept up-to-date with the passage of time since it uses the sysindexes table to get the total count of records.
select substring(o.name, 1, 30) Table_Name ,i.rows Number_of_Rows
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.xtype = 'u'
and i.indid < 2
order by o.name
November 4, 2005 at 12:26 pm
my favorite method to achieve this is:
dbcc showcontig with tableresults, no_infomsgs
Look at the "ROWS" column where indid = 1
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply