SQL Server Performance Forum – Threads Archive

List of All Temp Tables belong to the Same User

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

I think you can get values by querying against INFORMATION_SCHEMA.TABLES view. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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

Try select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=’any user’ _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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.

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

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

]]>