Temp Table List

  • texpic

    SSCertifiable

    Points: 5882

    Is there a stored procedure anyone has that will list all the active temp tables being used by all the databases in SQL Server?

  • Gail Shaw

    SSC Guru

    Points: 1004474

    This will get it for you. I don't know if there's a way to tie the name back to the creating SPID though. don't think so.

    SELECT * FROM tempdb..sysobjects WHERE NAME LIKE '#%' AND xtype = 'U'

    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
  • ChiragNS

    One Orange Chip

    Points: 26137

    Gail

    One doubt... This query returned tables with names like '#042A2C58' , '#0626B80C' etc. Iam sure these temp tables have not been created by the developers or any other users. Nor are they created inside any procs.

    "Keep Trying"

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Those are table variables. Unlike temp tables, the name that they are given in code has no relation to the name assigned in TempDB.

    It's also possible that temp tables created by various system processes appears in the list. I'm not sure if there's any way to find the process that created a temp object, and thereby filter out ones created by the system.

    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
  • ChiragNS

    One Orange Chip

    Points: 26137

    Thanks Gail. :).

    "Keep Trying"

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43696

    just for fun:

    select name

    from tempdb..sysobjects

    where name like '#%' --> table variables

    and name not like '#[A-Z,a-z]%' --> temp tables

    select name

    from tempdb..sysobjects

    where name like '#[A-Z,a-z]%' --> temp tables

    select name

    from tempdb..sysobjects

    where name like '#%' --> temp tables and table variables

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43696

    I just ran into an old article - it seems to be the 'holy grail' (or at least a piece of it) regarding tebp table creation ...

    https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

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