SQL Server Performance

List of All Temp Tables belong to the Same User

Discussion in 'General DBA Questions' started by fbatakci, Nov 6, 2003.

  1. fbatakci New Member


    One of my stored procedures creates a lot of temporary tables with different names and different columns, and the number of temporary tables changes from 0 to 100 according to queried data. What I would like to do is to get all temporary table names that I created by querying tempdb..sysobjects. The problem is if another user runs the procedure at the same time he/she will create temp tables with the same names in his/her session and when you query the tempdb..sysobjects, you will see the other users' temp table names. I want to get the names that belongs to my session only.

    Thanks in advance
  2. satya Moderator

    I think you can get values by querying against INFORMATION_SCHEMA.TABLES view.

    Satya SKJ
  3. satya Moderator

    I think you can get values by querying against INFORMATION_SCHEMA.TABLES view.

    Satya SKJ
  4. fbatakci New Member


    select * from INFORMATION_SCHEMA.TABLES (sa) shows all temp tables that belong to all users.
    select * from INFORMATION_SCHEMA.TABLES (any user) does not show any temp table information.

    Database Administrator
  5. satya Moderator

    Try select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='any user'

    Satya SKJ
  6. fbatakci New Member

    Hi Satya,

    These are the steps How I tried INFORMATION_SCHEMA.TABLES view;
    I created a login named "del2" and gave the db_owner permission on the database named "del2db".
    I logon the system as user "del2". I run the following query: "select 5 w into #w". It created a temp table named "#w" having 1 column named "w" with value 5. When I used "select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='del2'" I got nothing. I changed the database by using "use tempdb" and I ran the query "select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='del2'" again and still I got nothing.
    I found another method like getting object_name of every table in the 'tempdb..sysobjects'. If you do not have permission to see the table names it returns null for that table and a valid name for tables that belong to you.

    Thanks for your help.
  7. ChrisFretwell New Member

    Glad you found a way fbatakci.
    I think the reason your select returned nothing is that user 'del2' if dbo, and thats how its stored in table_schema view. So if you ran select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbo' you would like get tables back, but you would also get all dbo tables, not just those created by that session.

  8. fbatakci New Member

    Hi All,

    First I appreciated for your help. Below query is the solution for my question. When you run the query, it will only show the temp tables belong to you, it does not matter if you are dbo, sysadmin or any other normal user, it will only show your temp tables.

    left(name, charindex('_',name)-1)
    charindex('_',name) > 0and
    not object_id('tempdb..'+name) is null

    Thanks Chris, Satya
    Have a great day..

Share This Page