SQL Server Performance

what is wrong with table variable

Discussion in 'General Developer Questions' started by ranjitjain, Jun 23, 2005.

  1. ranjitjain New Member

    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
  2. FrankKalis Moderator

    Does it need to be a table variable?
    This works for me


    set nocount on
    create table [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 [table] select ''' + @stmt + ''', count(*) FROM '+@stmt
    print @stmt
    exec sp_executesql @stmt
    select @err = @@ERROR
    if @err = 0
    begin
    print 'executed: ' + @stmt
    SET @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 [table]
    union
    Select (@@identity+ 1) id,'Total Rows',sum(cnt) totalDatabaseRows from [table]
    drop table [table]
    set nocount off
    GO


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  3. dineshasanka Moderator

    I guess you can't use table variable inside the sp_executesql
  4. dineshasanka Moderator

  5. FrankKalis Moderator

    The link is broken [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  6. dineshasanka Moderator

  7. Madhivanan Moderator

  8. Madhivanan Moderator

    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
  9. ranjitjain New Member

    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(*).
  10. ranjitjain New Member

    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.
  11. Madhivanan Moderator

    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
  12. ranjitjain New Member

    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.
  13. dineshasanka Moderator

    Yes it is advisable to not to use system tables as far as possible
  14. Madhivanan Moderator

    I wonder why there is no view in the Master database to have this information


    Madhivanan

    Failing to plan is Planning to fail
  15. ranjitjain New Member

    <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=':D' />]

Share This Page