Post reply

How to know who has created a temporary table in tempdb?

  • June 29, 2011 at 11:39 pm

    #240065

    Hello -

    Can someone help me to know who has created a temp table in tempdb?

    I wanted to know which user has executed the script to create a temp table in tempdb. Asof now, I have only tempdb with me but I am open if you guys can guide me or point me somewhere where I can find answer to my question.

    Please let me know if we can know somewhere (if not in tempdb) else in the database.

    Appreciate your help..!!

    Thanks,

    Sanjeev.

  • June 30, 2011 at 3:45 am

    #1346402

    SELECT

    SUSER_NAME(CONVERT(INTEGER, OBJECTPROPERTYEX(t.object_id, 'OwnerId')))

    FROM tempdb.sys.tables AS t

    WHERE

    t.name LIKE N'#banana%';

    ...assuming you were looking for temporary tables named '#banana'.

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • June 30, 2011 at 4:01 am

    #1346412

    Thanks Paul,

    I logged in using SA account and created a temp table and ran the query on TempDB ....result as expected. And my expressions were :smooooth:

    BUT then I logged in using my personal account (domain\username) and created one more temp table and ran the same query but this time result was SA. Not sure if I am missing something here or doing wrong.

    Could you please help me out here ...Appreciate your help ...

    Thanks,

    Sanjeev.

  • June 30, 2011 at 4:21 am

    #1346420

    It seems temporary tables always have an OwnerId of 1.

    That's inconvenient; looks like you are out of luck.

    🙁

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • June 30, 2011 at 4:28 am

    #1346423

    Thanks buddy ...for spending time on this ...appreciate this.

    Thanks,

    Sanjeev.

  • July 3, 2011 at 5:34 pm

    #1347592

    sys.dm_db_task_space_usage

    the space usage is only for tempdb, so any session with an entry here is consuming tempdb space.

    Session_id corresponds to session_id in sys.dm_exec_connections and sys.dm_exec_sessions.

    Eddie Wuerch
    MCM: SQL

Viewing 6 posts - 1 through 5 (of 5 total)

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