count(*)

[Tekreader]

Can Anyone Tell me is there any script to get number of records in each table in a database for Sqlserver2000.

Thanks
TekReader

 

[gmmastros]

Create Table #Counts(TableName varchar(200), Rows Int)

exec sp_msforeachtable 'Insert Into #Counts(TableName, Rows) Select ''?'' As Table_Name,Count(*) From ?'

Select * From #Counts
Drop Table #Counts

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheSQL]

CREATE TABLE TblRowCount(TblName varchar(255) NOT NULL,
			RowsCounted int NOT NULL)

sp_MSforeachtable @command1="INSERT TblRowCount SELECT '?' TableName,count(*) from ?"

SELECT * FROM TblRowCount

You could do it without the create table bit, but I'm assuming that you want to do something with the data once you've finished the rowcount.

 

[TheSQL]

George beat me to it. ;-)

 

[gmmastros]

only by 2 minutes.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

If you are going to create a permanent table with this information, I suggest you also insert a batch number and datetime of the insert sothat you can identify when the table had that count and compare counts over time.

"NOTHING is more important in a database than integrity." ESquared