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

Examples

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.

SettingDescription
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:

InformationValues
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