sp_tempdb

Description

sp_tempdb allows users to:

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.

Notetempdb 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)

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


show and who


create and drop


add and remove

Permissions

By default, only the System Administrator or users with the SA role can execute sp_tempdb.

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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