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.

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” ] | 
	[ "unbindall_gr", "groupname" ] |
	[ show [, "all" | "gr" | "db" | "login" | "app" [, “name” ] ] | 
	[ who, “dbname” ] |
	[ help ] ]

• create – creates a temporary database group.

• drop – drops a temporary database group.

• groupname – is the name of the temporary database group.

• add – adds a temporary database to a temporary database group.

• remove – removes a temporary database from a temporary database group.

• tempdbname – is the name of the temporary database you are adding or removing.

• bind –binds logins and applications to a temporary database or temporary database group.

• unbind – unbinds logins and applications to a temporary database or temporary database group.

• instance_name – is the name of the instance owning the local temporary database that is to be unbound. This option is for the Cluster Edition only.

• 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

• 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.

• unbindall_gr groupname – removes all login and application bindings for a given temporary database group.

• 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 all temporary database groups, all database-to-group memberships, and all login and application bindings.

  • gr – displays all temporary database groups. If you specify name, displays all members of the group.

  • db – displays all 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, only the bindings for the login name are printed.

  • app – displays all bindings where the application is not NULL. If you provide name, the bindings for the application name are printed.

• 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”.

• For the Cluster Edition, tempdbname must be a local user temporary database.