October 31, 2006 at 6:52 am
A simple stored procedure loops through all tables in a database as shown below.
I'm stumped by the fact that in this loop I don't seem to be able to get a record count of the current table using exec () or sp_executesql. With the latter you can pass variables but you can't get anything coming back. What am I missing here?
declare tabs cursor for
select name
from dbo.sysobjects
where type='U' and ...
order by name
declare @tabname varchar(50)
declare @recs int
open tabs
fetch next from tabs into @tabname
while @@fetch_status=0
begin
set @stmt='select @recs=count(*) from '+@tabname
exec sp_executesql @stmt
<<<do something if table contains at least one record>>>
fetch next from tabs into @tabname
end
close tabs
deallocate tabs
October 31, 2006 at 7:19 am
Check this out :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=285646
Also if and estimation is enough for you, you can use the sysindexes tables :
SELECT
O.Name
, MAX(i.rowcnt) AS RowCounts
FROM dbo.Sysindexes I
INNER JOIN dbo.SysObjects O
ON I.id = O.id
WHERE I.indid IN (0,1)
AND o.XType = 'U'
--HAVING MAX(i.rowcnt) > 0
GROUP BY O.Name
ORDER BY O.Name
If you need the actual count, you can run sp_updatestats before running that query... but a table scan is safer in that case.
October 31, 2006 at 8:32 am
Well at least with this query I can get at the row counts.
But the stuff at that link is pretty murky. I can't make heads or tails out of it and apply it to my original problem. It seems to be very esoteric use of EXEC () and sp_executesql. But a good night's sleep should help...
November 1, 2006 at 6:01 am
Slept on it. Decided to use VB+ADO. SQL Server dynamic stored procedures are simply not up to this without giving you some serious and unnecessary headaches.
November 1, 2006 at 6:32 am
select row from sysindexes where id=object_id('table_name') and indid=0
is better than
select count(*) from table_name
because the 2nd -scans the whole table whereas the 1st - contains the value itself
November 2, 2006 at 8:01 am
Did some searching on 'dynamic sql' and came up with this as the solution to my original problem.
declare @tabname nvarchar(50)
declare @count int
declare @sql nvarchar(4000)
select @tabname='sometable'
select @sql=N'select @cnt=count(*) from '+@tabname
exec sp_executesql @sql,N'@cnt int output',@cnt=@count output
print @count
It's the "@cnt=@count output" part that refuses to sink into my brain. BOL says that from the third position onwards you supply values for the parameters. Under normal circumstances this means that the stuff on the right hand side gets assigned to the variable on the left hand side. But with the 'output' modifier you make it go the other way?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply