Want to do a record count on all tables... 1

[nodwag]

Hi...

Essentially, I want to do a record count for all tables within a database.

I've done the following, but get an error that @sCurrTable is not defined. I'm assuming this is within the Dynamic SQL, but not sure why.

Any advice would be greatly appreciated.

CREATE PROCEDURE phswc_table_info_extraction
AS

DECLARE @sTableName VARCHAR(64)
DECLARE @sCurrTable VARCHAR(64)
DECLARE @nRowCount INTEGER
DECLARE @sSQL NVARCHAR(500)
DECLARE @sSQLParam NVARCHAR(500)

DECLARE csr_tablelist CURSOR FOR
SELECT name
FROM sysobjects
WHERE type = 'U'
ORDER BY name

OPEN csr_tablelist

DELETE FROM aa_mp2_tablestats

SET @sSQL = N'SELECT @nRowCount = COUNT(*) FROM @sCurrTable'
SET @sSQLParam = N'@sCurrTable VARCHAR(64), @nRowCount INT OUTPUT'

FETCH NEXT FROM csr_tablelist
INTO @sTableName

WHILE @@FETCH_STATUS = 0
BEGIN

EXECUTE sp_executesql @sSQL, @sSQLParam, @sCurrTable = @sTableName, @nRowCount = @nRowCount OUTPUT

INSERT INTO aa_mp2_tablestats(table_name, record_count)
VALUES(@sTableName, @nRowCount)

FETCH NEXT FROM csr_tablelist
INTO @sTableName
END

CLOSE csr_tablelist
DEALLOCATE csr_tablelist

GO


DGaw
Independent Consultant
Seattle, WA

 

[vongrunt]

Two SETs (@sSQL and @sSQLParam) should be inside WHILE loop. And then you'll have to use @sTable name to dynamically build @sSQL.



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

Sysindexes should save you alot of effort.

try

select sum(rowcnt) from sysindexes where indid in (0,1)

 

[NoCoolHandle]

Sorry that will also have you counting rows in the system tables try instead

select sum(rowcnt) from sysindexes where indid in (0,1) and id > 50000

 

[gmmastros]

You could use the undocumented sp_msforeachtable function.

Create Table #Temp(RecordCount Integer)

Insert Into #Temp
Exec sp_msforeachtable 'Select Count(*) As RecordCount From ?'

Select Sum(RecordCount) As SumOfRecordsForAllTables from #Temp

Drop Table #Temp


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Nice one NoCoolHandle. I like your solution better than mine. They both produce the same number, but yours runs faster.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nodwag]

Thanks for all the suggestions, guys.

Vongrunt pointed out my main problem. ...with the code that is.

My real problem is that I've been up waaay too long and got stuck in one of those 'stupid-loops'.

D

DGaw
Independent Consultant
Seattle, WA

 

[vongrunt]

IIRC some people reported that sysindexes.rowcnt isn't always accurate realtime. Other than that, yes - cool trick.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[nodwag]

Had also read that about sysindexes.rowcnt, so that's why I used this particular approach. Found some other code online for updating the Statistics, but it involved more effort that it was worth for what I needed to do. Good for general interest sake, but not when just trying to cram a solution out the door...

DGaw
Independent Consultant
Seattle, WA

 

[gmmastros]

vongrunt

Have you ever heard about problems with the sp_msforeachtable approach?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

sp_msforeachtable also runs cursor

If there is any "problem" (or better said difference) it is about code that counts records, not looping method.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

After doing a small analysis..

Doing a sum of the rowcnt on every table in nwind = 14% cost of batch.

Doing a count of each tables rows by index scan = 84% and
northwind doesn't have any big tables.

My only question is.. if you rebuild your indexes or run sp_spaceused 'update usage' priodicly what is 100% accuracy worth to you? Are you looking for 100% accuracy or will 98% work for you (especiually if it only takes a fraction of the time and almost none of the expence of doing an acutal count of every row in an index?)

And ... i really don't care. but concurrent activity can be really bad. And if you rebuild indexse the issues don't pop up. And rebuilding indexes is ALWAYS good for query (select) preformance. so.......