Two options of sp_tempdb are enhanced:
sp_tempdb show,'db' sp_tempdb who,'db_name'
This option displays all temporary databases and the names of the groups to which the temporary databases belong. The following SQL commands illustrate this:
create temporary database mytempdb ------------- CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'master'.| create temporary database mytempdb1 ---------- CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'master'. sp_tempdb 'add', mytempdb,'default' ------------ (return status = 0 sp_tempdb show, db ------------- Database Group -------- tempdb default mytempdb default mytempdb1 (3 rows affected) (return status = 0)
This option displays the login and application names of all active sessions assigned to specified temporary databases.
sp_addlogin anunay, anunay
---------------
sp_tempdb "bind", lg, sa, DB, mytempdb3
-------------
(return status = 0)
sp_tempdb "bind", lg, anunay, DB, mytempdb3
-----------------
(return status = 0)
starting sessions
--------------------
${ISQL} -J -U anunay -P anunay -I${SYBASE}/interfaces -w200
sp_tempdb who, mytempdb3
-------------
spid loginame application
------ -------- -----------
11 sa isql
13 anunay isql
(2 rows affected)
(return status = 0)
