sp_tempdb

Description

sp_tempdb allows users to:

• Create and manage temporary database groups.

• Bind users or applications to the default or other temporary database group or to a specific local temporary database.

• Manage bindings to local temporary databases and temporary database groups.

These bindings are stored in the sysattributes table in master database.

sp_tempdb provides the binding interface for maintaining bindings in sysattributes that are related to the multiple temporary database.

Syntax

sp_tempdb [
	[ { “create” | “drop” } , “groupname” ] | 
	[ { “add” | “remove” } , “tempdbname”, “groupname” ] | 
	[ { “bind”, “objtype”, “objname”, “bindtype”, “bindobj” 
		[, “scope”, “hardness” ] } | 
		{ “unbind”, “objtype”, “objname” [, “scope” ] “instance_name”} ] | 
	[ “unbindall_db”, “tempdbname” ] | 
	[ show [, "all" | "gr" | "db" | "login" | "app" [, “name” ] ] | 
	[ who, “dbname”] 
	[ help ] ]

Parameters

Examples

Example 1

Adds mytempdb1 to the default group:

sp_tempdb "add", "mytempdb1", "default"

Example 2

Removes mytempdb1 from the default group:

sp_tempdb "remove", "mytempdb1", "default"

Example 3

Binds login “sa” to the default group:

sp_tempdb "bind", "lg", "sa", "GR", "default"

The value for objtype in this example is login_name. You can substitute login_name with lg or LG.

The value for bindtype in this example is group. You can substitute group with gr or GR.

Example 4

Changes the previous binding of login “sa” from the default group to mytempdb1:

sp_tempdb "bind", "lg", "sa", "DB", "mytempdb1"

The value for bindtype in this example is database. You can substitute database with db or DB.

Example 5

Binds isql to mytempdb1:

sp_tempdb "bind", "ap", "isql", "DB", "mytempdb1"

The value for objtype in this example is application_name. You can substitute application_name with ap or AP.

Example 6

Changes the previous binding of isql from mytempdb1 to the default group:

sp_tempdb "bind", "ap", "isql", "GR", "default"

Example 7

Removes the bindings of login “sa” and application “isql”.

sp_tempdb "unbind", "lg", "sa"

sp_tempdb "unbind", "ap", "isql"

Example 8

Removes all login and application bindings for the mytempdb1 database:

sp_tempdb "unbindall_db", "mytempdb1"

Example 9

Demonstrates the sp_temp show command. A selection of the different variations is chosen, and abbreviated sample output is displayed.

sp_tempdb show

Temporary Database Groups 
------------------------------- 
default 

Database                        GroupName 
------------------------------- ----------------
tempdb                          default 
mytempdb                        default 
mytempdb1                       default 
mytempdb2                       default 
mytempdb3                       default 

Login   Application  Group    Database    Hardness
------- ------------- -------- ----------- -------- 
NULL   isql          default  NULL        SOFT 
sa      NULL          NULL     mytempdb3   HARD 

Example 10

Displays the default temporary database group:

sp_tempdb show, "gr"

Temporary Database Groups
-------------------------------
default

Example 11

Displays all the temporary database group names that are bound to the default group:

sp_tempdb show, "gr", "default"Member Databases
------------------------------- 
tempdb
mytempdb
mytempdb1
mytempdb2
mytempdb3

Example 12

Displays all the databases-to-group memberships:

sp_tempdb show, "db"

Database              Group
--------------------- ----------------
tempdb                default
mytempdb              default
mytempdb1             default
mytempdb2             default
mytempdb3             default

Example 13

Displays all the databases-to-group memberships for the mytempdb1 database.

sp_tempdb show, "db", "mytempdb1"

Database              Group
--------------------- ----------------
mytempdb1             default

Example 14

Displays all the login bindings where login is not NULL:

sp_tempdb show, "login"

Login   Application   Group   Database    Hardness
------- ------------- ------- ----------- --------

sa      NULL          NULL    mytempdb3   HARD

Example 15

Displays all active sessions that are assigned to the system tempdb:

sp_tempdb who, "tempdb"

spid   loginame
------ ------------------------------
2      NULL
3      NULL
4      NULL
5      NULL
6      NULL
7      NULL
8      NULL

Example 16

Displays all active sessions that are assigned to the mytempdb3 user-created temporary database:

sp_tempdb who, "mytempdb3"

spid   loginame
------ ------------------------------ 
17      sa

Example 17

Displays usage information:

sp_tempdb help

Usage: 
sp_tempdb 'help' 
sp_tempdb 'create', <groupname> 
sp_tempdb 'drop', <groupname> 
sp_tempdb 'add', <tempdbname>, <groupname> 
sp_tempdb 'remove', <tempdbname>, <groupname> 
sp_tempdb 'bind', <objtype>, <objname>, <bindtype>, <bindobj>, <scope>, 
          <hardness> 
sp_tempdb 'unbind', <objtype>, <objname>, <scope> 
sp_tempdb 'unbindall_db', <tempdbname> 
sp_tempdb 'show', <command>, <name> 
sp_tempdb 'who', <dbname> 

<objtype> = ['LG' ('login_name') | 'AP' ('application_name')]; 
<bindtype> =['GR' ('group') | 'DB' ('database')] 
<hardness> = ['hard' | 'soft'] 
<command> = ['all' | 'gr' | 'db' | 'login' | 'app'] 

Example 18

Displays all temporary databases and the names of the groups to which the temporary databases belong:

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)

Example 19

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) 

Usage

• To display the distribution of users across all temporary databases, use both options, show and who:

  • To obtain the names of all temporary databases, execute

    sp_tempdb 'show' 
    

  • Pass each temporary database name to

    sp_tempdb 'who', tempdbname
    

  In Adaptive Server versions 15.0 and above, you can obtain the same output by executing sp_who.

• When using the sp_tempdb create stored procedure, the groupname variable:

  • Must be a valid identifier

  • Cannot already exist

  The default group is the system-generated group, of which tempdb is always a member. This default group is present if you:

  • Upgrade using the Adaptive Server containing this feature, or

  • Create a new master device.

  If the default group is not present, you can create it by using:

  sp_tempdb create, "default"
  

  An error message displays if you attempt to create a default group that already exists.

• To add a temporary database to the default temporary database group, both the temporary database and the group name must already exist. When you use sp_tempdb add to add a tempdbname to a set of databases that are members of the default temporary database group, tempdbname becomes available for round-robin assignment from within that group.

  sp_tempdb add fails if tempdbname is not already part of the global list of available temporary databases in Adaptive Server.

  User-created temporary databases need not belong to the default temporary database group. The system tempdb is implicitly a member of the default group.

  If you try to add a temporary database to the default temporary database group when it is already a part of that group, you get an error message, and no changes take place in sysattributes.

Permissions

The permission checks for sp_tempdb differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are: