Row count of all tables 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

Transact-SQL (2000)

Row count of all tables in a database

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-05-30 : 15:50:56

I thought I saw somewhere that you could return a result set from the Master database that would contain the table names and total records for all tables in a database. Anybody know? If not, is there an easy way to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-30 : 15:58:51

SET NOCOUNT ON

USE DB1

SELECT 'SELECT COUNT(*) FROM ' + name
FROM sysobjects
ORDER BY name

Just copy the output into another window and run it.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 16:08:04

Don't forget NOLOCK

SET NOCOUNT ON

USE DB1

SELECT 'SELECT COUNT(*) FROM ' + name + ' (NOLOCK)'
FROM sysobjects
ORDER BY name

Brett

8-)

izaltsman
A custom title

1139 Posts

Posted - 2003-05-30 : 16:10:52

If you have lots of data in your db, counting records in this manner will seriously hurt performance. I prefer doing something like


SELECT o.name, i.rows 
FROM sysindexes i
	inner join  sysobjects o 
		on i.id = o.id
WHERE  indid<2 
	AND o.xtype ='U'

Your statistics need to be up to date in order for this method to be accurate though.

Edited by - izaltsman on 05/30/2003 16:13:10

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-05-30 : 16:15:31

Thanks! Exactly what I was looking for...

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 16:36:38

Or...

SELECT 'sp_spaceused [' + TABLE_NAME + '] '+ CHAR(10)+'GO'
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

But stats need to be up to date as well....

Just an option, but I like izaltsman suggestion better I think..

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-30 : 17:22:53

www.nigelrivett.com
Retrieve the number of rows in each table in a database

Oh well - I'll copy it here
It's done a table at a time in case you want to get other info on the tables.

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_GetRowsForAllTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetRowsForAllTables]
GO

Create Procedure sp_GetRowsForAllTables
@DBName varchar(128) = null
as

set nocount on
if @DBName is null
set @DBName = db_name()

create table #a
(TableName varchar(128), norows int null, id int identity(1,1))

declare @id int ,
@maxID int ,
@TableName varchar(128) ,
@FKName varchar(128) ,
@cmd nvarchar(1000) ,
@rc int,
@spcmd varchar(1000)

set @cmd = 'exec ' + @DBName + '..sp_executesql N''insert #a (TableName)
select TABLE_NAME from information_schema.tables
where TABLE_TYPE = ''''BASE TABLE'''' ''
'
exec (@cmd)

select @id = 0 ,
@maxID = max(id)
from #a

while @id < @maxID
begin
select @id = min(id)
from #a
where id > @id

select @TableName = TableName
from #a
where id = @id

set @cmd = 'exec ' + @DBName + '..sp_executesql N''update #a set norows = (select rows from sysindexes where indid in (0,1) and id = object_id(''''' + @TableName + '''''))'
set @cmd = @cmd + ' where #a.id = ' + convert(varchar(10),@id) + ''''

exec (@cmd)
if @rc <> 0 or @@error <> 0
begin
raiserror('failed %s',16,-1,@TableName)
return
end
end

select * from #a

drop table #a
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 05/30/2003 17:24:34

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-09 : 13:45:53

Just a (few) question about this and auto statistics option. I thought I had an understanding, but....

1. Is it good or bad to have auto statistics On?
2. If not, do you need to run Update statistics on a scheduled basis?
3. is sp_spaceused dependant on updated statistics
4. Can SELCT Count(*) and sp_space used be out of synch with auto stats on?
5. What's create statistics used for, if stats are created for tables and indexes upon their creation?

Any info appreciated.

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-09 : 13:50:37

quote:

1. Is it good or bad to have auto statistics On?

Not bad, but maybe not good in certain situations where updating them during peak hours causes performance problems.

quote:

2. If not, do you need to run Update statistics on a scheduled basis?

Yes

quote:

3. is sp_spaceused dependant on updated statistics

Yes

quote:

4. Can SELCT Count(*) and sp_space used be out of synch with auto stats on?

COUNT(*) will not be out of synch. sp_spaceused can be so you can run:
sp_spaceused @UPDATEUSAGE = TRUE or

DBCC UPDATEUSAGE(DB1)
sp_spaceused

quote:

5. What's create statistics used for, if stats are created for tables and indexes upon their creation?

I have wondered this too.

Tara

Edited by - tduggan on 06/09/2003 13:52:22

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-09 : 13:55:17

Thatks Tara...kind of got turned around on this..but specificaally

quote:

4. Can SELCT Count(*) and sp_space used be out of synch with auto stats on?

If auto stats are on, can and when will they be out of synch?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-09 : 13:57:55

COUNT(*) will not be out of synch. sp_spaceused can be out of synch but I'm not sure when that occurs. Probably when a new page is allocated for any object.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-09 : 14:16:41

Create statistics allows you to also create statistics for columns.
I suspect that the index tuning wizard (or something else) does this as there have been a few questions about statistics appearing in sysindexes for all columns but without the name being _WA_sys%.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-09 : 14:30:54

quote:

COUNT(*) will not be out of synch. sp_spaceused can be out of synch but I'm not sure when that occurs. Probably when a new page is allocated for any object.

Tara

I meant with each other...but thanks for the input everyone.

Brett

8-)

wilso_s
Starting Member

10 Posts

Posted - 2003-06-10 : 13:13:29

Here's what I've used:

CREATE Procedure uspLogAllTableSizes
As

declare @dbName varchar (128)
declare @command varchar (128)

create table #tmpPrelimTableSizes (
name varchar(128),
rows int,
reserved varchar(16),
data varchar (16),
index_size varchar (16),
unused varchar(16)
)

create table #tmpTableSizesConverted (
dbName varchar(128),
tblName varchar(128),
rows int,
reserved int,
data int,
index_size int,
unused int
)

create table #tmpC (
db varchar (128), --databasename
cmd varchar (256), --command to run
flag int default 0
)

--@command1 can only be 128 characters long.
--insert into #tmpC all the database names and commands to run
exec sp_msforeachdb @command1 = "insert into #tmpC (db, cmd) select '?', 'exec

..sp_MSforeachtable @replacechar=''~'', @command1=''sp_spaceused ''''~'''''''"

delete from #tmpC where db = 'tempdb'

set @dbName = (select top 1 db from #tmpC where flag = 0)
set @command = (select cmd from #tmpC where db = @dbName)

UPDATE #tmpC Set Flag = 1 Where db = @dbName

WHILE @dbName is not null
BEGIN
--print @dbName
--print @command
insert into #tmpPrelimTableSizes
exec (@command)

insert into #tmpTableSizesConverted (tblName, rows, reserved, data, index_size, unused)
select name, rows,
convert(int, substring(reserved,1, charindex(' ', reserved, 1))),
convert(int, substring(data,1, charindex(' ', data, 1))),
convert(int, substring(index_size,1, charindex(' ', index_size, 1))),
convert(int, substring(unused,1, charindex(' ', unused, 1)))
from #tmpPrelimTableSizes

update #tmpTableSizesConverted set dbName = @dbName where dbName is null

insert into tblTableSizes (theDate, DatabaseName, TableName, [Rows], ReservedSize_KB,
DataSize_KB, IndexSize_KB, UnusedSize_KB)
select getDate(), dbName, tblName, rows, reserved, data, index_size, unused from #tmpTableSizesConverted

set @dbName = (select top 1 db from #tmpC where flag = 0)
set @command = (select cmd from #tmpC where db = @dbName)

UPDATE #tmpC Set Flag = 1 Where db = @dbName

truncate table #tmpPrelimTableSizes
truncate table #tmpTableSizesConverted

END

GO

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-11 : 09:04:51

Well varchar(128) is equivalant to (systems supplied UDD) datatype sysname...a table name could potentially take up all of your command string...

but hey, whatever floats your boat.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources