sp_helpdb

The status column of sp_helpdb includes these descriptions for database durability:

• user created temp db – normal temporary database created by the user (that is, created without specifying the durability parameter).
• user-created enhanced performance temp db – user-created temporary database created explicitly with the no_durability parameter. Because a database created with no_durability depends on licensing, it may not come online if the license expires.

For example, if you create this database:

create temporary database tempdb_explicit on default = 50 
with durability = no_recovery

sp_helpdb includes this output:

sp_helpdb tempdb_explicit
 name db_size owner dbid created durability lobcomplvl inrowlen status 
 ---- ------- ----- ---- ------- ---------- ---------- -------- ------ 
 
 tempdb_explicit  50.0 MB  sa  7    Dec 05, 2012 no_recovery  0  NULL
         select into/bulkcopy/pllsort, trunc log on chkpt, 
         mixed log and data, user-created enhanced performance 
         temp db, allow wide dol rows 

(1 row affected)
device_fragments   size      usage         created          free  kbytes 
----------------- ------ -------------- ------------------ ------------- 
 master          50.0 MB  data and log  Dec 5 2012 8:49PM    49216 
(return status = 0)