sp_tempdb

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

create – creates the default temporary database group.
drop – drops a temporary database group.
groupname – is the name of the temporary database group.
add – adds temporary databases to the default temporary database group.
remove – removes temporary databases from the default temporary database group.
tempdbname – is the name of the temporary database you are adding or removing. For the Cluster Edition, tempdbname must be a local user temporary database.
bind – binds logins and applications to temporary databases or the default temporary database group.
unbind – unbinds logins and applications to temporary databases or the default temporary database group.
objtype – is the object type. Valid values are:
- login_name (or LG)
- application_name (or AP)
Values are not case-sensitive.
objname – is the name of the object you bind or unbind.
bindtype – is the bind type. Valid values are:
- group (or GR)
- database (or DB)
Values are not case-sensitive.
bindobj – is the name of the object being bound, and is either a group or a database depending on the bindtype.
scope – NULL.
instance_name – in cluster environments – is the name of the instance owning the local temporary database that is to be unbound. This option is for the Cluster Edition only.
hardness – hardness – is hard, soft, or NULL. The default is soft. When you set the value of hardness to hard, a failure to assign a temporary database according to the binding results in a failure of the login.
When you set the value to soft, such a failure results in the assignment of a temporary database from the default group or a local system temporary database.
unbindall_db – removes all login and application bindings for a given temporary database. It does not remove any database to group memberships. The tempdbname variable is required with this option.
Existing assignments to active sessions are not affected by this operation.
show – displays information stored in the sysattributes table about the existing groups, group members, login and application bindings, and active sessions that are assigned to a given database. The values are:
- all or no argument – displays the default temporary database group, all database-to-group memberships, and all login and application bindings.
- gr – displays the default temporary database group. sp_tempdb show displays all temporary databases bound to the default temporary database group whether you specify “default” for the name option or not.
- db – displays all databases and temporary databases to group memberships. If you provide name, then only the database to group memberships for the database name are printed.
- login – displays all login bindings where login is not NULL. If you provide name, then only the bindings for the login name are printed.
- app – displays all bindings where the application is not NULL. If you provide name, then the bindings for the application name are printed.
Note: tempdb is always part of the default database group.
who – displays all active sessions assigned to the given temporary database. When using the who parameter, you must use:
- dbname – is the name of a temporary database. If you provide a nontemporary database name for dbname, sp_tempdb who executes, but does not report any active sessions bound to it.
  
  If system_view is set to cluster, all active sessions of the cluster are examined. If system_view is set to instance, sessions that are active on the current instance are examined
  
  This command may be executed from any instance in the cluster.
help – displays usage information. Executing sp_tempdb without specifying a command is the same as executing sp_tempdb “help”.

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)

Usage

There are additional considerations when using sp_tempdb:

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

Note: sp_tempdb add fails if tempdbname is not already part of the global list of available temporary databases in the SAP ASE 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.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage server` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

Auditing

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect