what is wrong with table variable

hi guys,
im trying to save count of all user tables and then display sum of all tables rowcount.
i tried this with temp table works fine but when i change it to @tablevariable gives error.[B)]

set nocount on
declare @count table(id int identity, tbName sysname, cnt int not null)
declare @stmt nvarchar(4000), @err int
set @err = 0
DECLARE TableList CURSOR FOR
select table_name from information_schema.tables
where table_type='BASE TABLE' order by table_name
OPEN TableList
FETCH NEXT FROM TableList INTO @stmt
WHILE (@@FETCH_STATUS=0 and @err=0)
BEGIN
print @stmt
Set @stmt='insert into @count select ''' + @stmt + ''', count(*) ' + @stmt + 'Count from ' + @stmt
print @stmt
exec sp_executesql @stmt
select @err = @@ERROR
if @err = 0
begin
print 'executed: ' + @stmt
FETCH NEXT FROM TableList INTO @stmt
end
else
print 'Error: ' + convert(nvarchar(100),@err) + ' occurred executing: ' + @stmt
END
CLOSE TableList
DEALLOCATE TableList
select * from @count
union
Select (@@identity+ 1) id,'Total Rows' tbName,sum(cnt) totalDatabaseRows from @count
set nocount off
GO

quote:Originally posted by Madhivanan

quote:
hi guys,
im trying to save count of all user tables and then display sum of all tables rowcount.
i tried this with temp table works fine but when i change it to @tablevariable gives error.

If this is your requirement, why do you go for Table variable or Cursor?

Read this link

http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=5583

and then run this

For getting rowcount for every table,


select SO.name as TableName,SI.rows as TableRows from sysindexes SI
inner join Sysobjects SO on SO.id=SI.id and SO.Xtype='U' where SI.indid<2
order by So.name

For sum of all table rows


select 'Total Rows',sum(SI.rows) as TableRows from sysindexes SI
inner join Sysobjects SO on SO.id=SI.id and SO.Xtype='U' where SI.indid<2

Madhivanan
Failing to plan is Planning to fail

Thanx madhivanan,
But accessing rows from system table means those have to be updated,
so no guarantee to get the right result, so to make sure for accurate result i need to update statistics or else i have to go for count(*), i think we have discussed this thing in one of dinesh's post.
So i went for count(*).

Thanx a lot guys, as i said i created temp table then it works fine for me
but when i change it to table variable, it produces error,
so i was surprised and just wanted to confirm whether my logic is wron or code is wrong, or there is something which i dont know.

So i didnt knew that sp_executesql can't execute dynamic table variable.
so i feel this is a drawback so i again have to choose temp tables.
is there any other thing with which i can replace temp tables to get the same result.

quote:Originally posted by Madhivanan

quote:But accessing rows from system table means those have to be updated

Yes, thats why I specified that link
Update them and use that query
If your table has millions of rows and if you use count(*), it may slow the process
I think accessing sysindexes table is the easiest way in your case.
Madhivanan
Failing to plan is Planning to fail

yes i checked the link and i noticed satya was against querying system table, he preffered count(*) even if it returns result after long stress.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />I wonder why there is no view in the Master database to have this information<br />Madhivanan<br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />yes view on this information would be highly appreciable.[<img src='/community/emoticons/emotion-2.gif' alt='' />]