sp_tempdb allows users to:
Create the default temporary database group
Bind temporary databases to the default temporary database group
Bind users and applications to the default temporary database group or to specific temporary databases
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.
sp_tempdb [
[ { “create” | “drop” } , “groupname” ] |
[ { “add” | “remove” } , “tempdbname”, “groupname” ] |
[ { “bind”, “objtype”, “objname”, “bindtype”, “bindobj”
[, “scope”, “hardness” ] } |
{ “unbind”, “objtype”, “objname” [, “scope” ] } ] |
[ “unbindall_db”, “tempdbname” ] |
[ show [, "all" | "gr" | "db" | "login" | "app" [, “name” ] ] |
[ who, “dbname”]
[ help ] ]
creates the default temporary database group.
drops a database group.
is the default database group. Use “default”.
adds temporary databases to the default temporary database group.
removes temporary databases from the default temporary database group.
is the name of the temporary database you are adding or removing.
binds logins and applications to temporary databases or the default temporary database group.
unbinds logins and applications to temporary databases or the default temporary database group.
is the object type. Valid values are:
login_name (or LG)
application_name (or AP)
Values are not case-sensitive.
is the name of the object you bind or unbind.
is the bind type. Valid values are:
group (or GR)
database (or DB)
Values are not case-sensitive.
is the name of the object being bound, and is either a group or a database depending on the bindtype.
NULL.
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 the system tempdb.
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.
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.
tempdb is always part of the default database
group.
displays the login and application names of all active sessions assigned to the given temporary database. When using the who parameter, you must usedbname, which 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.
displays usage information. Executing sp_tempdb without specifying a command is the same as executing sp_tempdb “help”.
Adds mytempdb1 to the default group:
sp_tempdb "add", "mytempdb1", "default"
Removes mytempdb1 from the default group:
sp_tempdb "remove", "mytempdb1", "default"
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.
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.
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.
Changes the previous binding of isql from mytempdb1 to the default group:
sp_tempdb "bind", "ap", "isql", "GR", "default"
Removes the bindings of login “sa” and application “isql”.
sp_tempdb "unbind", "lg", "sa"
sp_tempdb "unbind", "ap", "isql"
Removes all login and application bindings for the mytempdb1 database:
sp_tempdb "unbindall_db", "mytempdb1"
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
Displays the default temporary database group:
sp_tempdb show, "gr"
Temporary Database Groups ------------------------------- default
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
Displays all the databases-to-group memberships:
sp_tempdb show, "db"
Database Group --------------------- ---------------- tempdb default mytempdb default mytempdb1 default mytempdb2 default mytempdb3 default
Displays all the databases-to-group memberships for the mytempdb1 database.
sp_tempdb show, "db", "mytempdb1"
Database Group --------------------- ---------------- mytempdb1 default
Displays all the login bindings where login is not NULL:
sp_tempdb show, "login"
Login Application Group Database Hardness ------- ------------- ------- ----------- -------- sa NULL NULL mytempdb3 HARD
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
Displays all active sessions that are assigned to the mytempdb3 user-created temporary database:
sp_tempdb who, "mytempdb3"
spid loginame ------ ------------------------------ 17 sa
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']
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)
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)
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', dbname
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.
By default, only the System Administrator or users with the SA role can execute sp_tempdb.
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 |
|
