SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to find out qty of row from all tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Clages1
Yak Posting Veteran

65 Posts
Posted - 06/27/2007 :  09:46:09  Show Profile  Reply with Quote
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

Slovenia
11741 Posts
Posted - 06/27/2007 :  10:00:10  Show Profile  Visit spirit1's Homepage  Reply with Quote
exec sp_MSForEachTable 'select ''?'' as TableName, count(*) as RowCnt from ?'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Edited by - spirit1 on 06/27/2007 10:01:46
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 06/27/2007 :  10:27:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Clages1
Yak Posting Veteran

65 Posts
Posted - 06/27/2007 :  17:58:18  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 06/28/2007 :  05:30:53  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 06/28/2007 :  06:13:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also try the method I suggested

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000