Records Count 1

[revvinghigh]

Need help on how to return all USER TABLES with its records count in 1 script?

thanks in advance!

 

[philhege]

Enjoy.

select b.status,a.table_name, 0 as recordcount, 0 as processed into #tablelist from information_schema.tables a
join sysobjects b on a.table_name = b.name
where b.status > 0 order by table_name
declare @table_name varchar(128), @sql varchar(512)
create table ##countholder (recordcount int,tablename varchar(128),)
while (select count(*) from #tablelist where processed = 0) > 0
begin
select top 1 @table_name = table_name from #tablelist where processed = 0
print @table_name
set @sql ='insert into ##countholder
select count(*), '''+@table_name+''' from ['+ @table_name+'] '
exec(@sql)
update #tablelist set processed = 1, recordcount = b.recordcount from ##countholder b where table_name = b.tablename
end
select * from #tablelist
drop table #tablelist
drop table ##countholder

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[revvinghigh]

thank you, i'll give this is a shot.

 

[revvinghigh]

is there a function to see the difference between 2 datetime values?

thanks!

 

[revvinghigh]

sorry, I meant this question for Visual FoxPro

 

[philhege]

DateDiff()

BTW BOL is a fantastic resource for these questions. I'd get a copy if I were in your shoes.

Did the record count code work OK for you? From my experience with it, I'd probably create an index for the temporary file. That seems to be where any bottleneck may occur.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[revvinghigh]

yeap it did, thanks!

 

[JamesLean]

FYI, there is an undocumented procedure, sp_MSForEachTable, that lets you execute the same SQL against every table in a database. Using this makes this job much easier and quicker:

CREATE TABLE #tmp (
	table_name sysname,
	record_count int
)

INSERT #tmp
EXEC sp_msforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

SELECT * FROM #tmp
ORDER BY table_name

--James

 

[ESquared]

If you have your statistics updated regulary you can also use the system count for each table if you don't need them to be 100% exact. I can't remember where this is off the top of my head. Anyone?

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[Luvsql]

I have tried to run both the scripts listed above, but in the first I get error:

Invalid object name 'information_schema.tables'.

and in the second:

Could not find stored procedure 'sp_msforeachtable'.


It's works on our SQL 2000 Enterprise Edition server, but not our SQL 2000 Standard Edition server. ?

 

[Luvsql]

My bad. Our one server is binary, so it's sp_MSforeachtable.

 

[vongrunt]

If you have your statistics updated regulary you can also use the system count for each table if you don't need them to be 100% exact. I can't remember where this is off the top of my head. Anyone?

Table sysindexes, I think:

select A.name, B.rowcnt
from sysobjects A
inner join sysindexes B on A.id=B.id and B.indid in (0, 1)
where A.xtype = 'U'

indid=0 for hash tables, 1 for clustered index, both are mutually exclusive so if stats are accurate this should work.