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

    Hello,

    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
    Faruk
  2. satya Moderator

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

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. satya Moderator

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

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  4. fbatakci New Member

    Hi,

    I tried INFORMATION_SCHEMA.TABLES.
    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.

    Faruk
    Database Administrator
  5. satya Moderator

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

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  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.

    Chris
  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.

    select
    left(name, charindex('_',name)-1)
    from
    tempdb..sysobjects
    where
    charindex('_',name) > 0and
    xtype='u'and
    not object_id('tempdb..'+name) is null

    Thanks Chris, Satya
    Have a great day..

Share This Page