How to find out qty of row from all tables

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

SQL Server Development (2000)

How to find out qty of row from all tables

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-06-27 : 09:46:09

Hi

using
exec sp_spaceused AREA

I get this where 20 is number of Rows

Area 20 40 KB 16 KB 24 KB 0 KB

Using

select TABLE_NAME
from INFORMATION_SCHEMA.TABLES a
WHERE TABLE_TYPE = 'BASE TABLE'

I get all table names from a DATABASE

I would like to get something like this

TableName Qty Of Rows
aaaaaaa 123
bbbbbbb 422
ccccccc 555
etc

How can i do this?

In other words i would like to know what tables are emptys and
what arent.

Tks

C.lages

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-27 : 10:00:10

exec sp_MSForEachTable 'select ''?'' as TableName, count(*) as RowCnt from ?'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 10:27:34

or

DBCC Updateusage

Select object_name(id) as table_name, rows from sysindexes
where indid<2
order by object_name(id)

Madhivanan

Failing to plan is Planning to fail

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-06-27 : 17:58:18

Tks

I did not Know this SP
but i have used your ideia and fix to my needs
tks
Carlos Lages

use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
order by tablename
drop table #rowcount

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-28 : 05:30:53

sp_MSforeachtable is an undocumentde stored procedure and is not recommended in production use.
you have no support for it if something goes wrong.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-28 : 06:13:26

Also try the method I suggested

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources