SQL Server 2000

I'm trying recover the EstimatedRows column from the output of statements such as:

SET SHOWPLAN_ALL ON

GO

SELECT title AS Qty
FROM pubs..titles
WHERE price > 10.00

GO

SET SHOWPLAN_ALL OFF

entirely from within TSQL - ie without a client front end to parse the query plan resultset returned. Esentially I am trying to compare actual result counts returned to the number of rows estimated by the Query Optimiser (this is as part of a automated performance analysis module within a knowledge system).

So far I haven't found a way to recover the output from the above statement block into a structure such as temporary table which I can then interogate from within TSQL to recover the values I require.

Approaches such as

USE pubs

GO

CREATE PROCEDURE dbo.usp_dev_showplan

@sql_str VARCHAR (7000)

AS


SET @sql_str =

'SET SHOWPLAN_ALL ON
GO '
+ @sql_str
+
' GO
SET SHOWPLAN_ALL OFF'

EXEC (@sql_str)

GO

EXEC dbo.usp_dev_showplan 'SELECT titles FROM pubs..titles WHERE price > 10'

have failed ....

We've done it easily enough through a client and ADO - but want to wrap it all up in TSQL to run within the Agent to automatically log trend data over time.

Can anyone help?

Many Thanks
Steve