Post reply

# of tables in TempDB

  • July 6, 2016 at 8:06 am

    #327509

    Hi,

    Is there anyway to get the number of temp tables in TempDB and created by which application? I am working with a customer with high disk contention on TempDB drive.

    Regards,

    Yuji

  • July 6, 2016 at 9:59 am

    #1888726

    Have you tried:

    select * from tempdb.sys.objects

    ?

  • July 7, 2016 at 7:41 am

    #1888916

    select name, object_name(object_id) As ObjName,*

    from tempdb.sys.objects

    where name like '#%'

  • July 7, 2016 at 7:45 am

    #1888920

    You'll be able to see the objects, but you won't be able to tell what they are or where they came from.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 7, 2016 at 7:53 am

    #1888924

    Thanks a lot!

  • July 7, 2016 at 8:06 am

    #1888935

    sunita2912 (7/7/2016)

    select name, object_name(object_id) As ObjName,*

    from tempdb.sys.objects

    where name like '#%'

    Is going to give temp table, table variables, cached temp table shells and internal work tables, not just the number of active, in-use temp tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 7, 2016 at 11:43 pm

    #1889100

    select left(name, charindex('_',name)-1)

    from tempdb..sysobjects

    where charindex('_',name) > 0 and

    xtype = 'u' and not object_id('tempdb..'+name) is null

  • July 8, 2016 at 1:06 am

    #1889103

    johnwalker10 (7/7/2016)

    select left(name, charindex('_',name)-1)

    from tempdb..sysobjects

    where charindex('_',name) > 0 and

    xtype = 'u' and not object_id('tempdb..'+name) is null

    Thanks, and if I would like to know the top 10 temp tables and the number, how can I do it?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply