Microsoft SQL Server - Lesson 37: A Review of Transact-SQL Built-In Stored Procedures

	A Review of Transact-SQL Built-In Stored Procedures

Server-Related Stored Procedures

Introduction

To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Getting Information About the Server

To let you get many statistics about your Microsoft SQL Server installation, Transact-SQL provides the sp_monitor stored procedure, which produces many values. Here is an example of executing it:

Stored Procedure

It is important to know that the values produced by the sp_monitor stored procedure depend on many factors such as the capacities of the machine, the type of connection, and what is going on at the time this procedure executes.

Getting Information About a User

Imagine that at one time you want to know who is currently connected to your Microsoft SQL Server. Transact-SQL provides the sp_who stored procedure. This procedure lets you know who is (actually who are) currently using the computer(s) in your network, what computers they are using (this procedure gives you the name of the computer a person is using), what database the person is using (what database the user is connected to), etc.

The syntax of the sp_who stored procedure is:

sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

This stored procedure takes one argument, which is optional, which means you can execute this procedure without any argument. When it has executed, sp_who produces a window made of nine columns. One column (the fourth) is labeled loginame and another column (the fifth), labeled hostname, shows the name of the computer the user is using.

Observe the following example that executes the sp_who stored procedure:

Stored Procedure

Notice the values in the loginname. This shows the name of the domain followed by the user name of a person who is currently connected. The hostname shows the name of the actual computer the person is using.

As mentioned previously, the sp_who stored procedure produces a window with nine columns. There are some issues you should be familiar with (even slightly) in order to understand some of the results.

A process can be considered an application (like Notepad) or part of an application (like one part of an application that is ripping music from a CD while another part of the same application is playing music from the same CD). Thread programming, also called threading, is the operating system's way of allocating the computer resources (memory for example) to each process (or each application) that needs it.

One of the problems the operating system faces is that there may not be enough resources for all processes in the computer. One of the consequences is that sometimes some processes must wait; that is, sometimes a certain process A must wait for a certain resource (like a DVD player), especially if that particular resource is currently being used by another process B (maybe process B is currently playing a movie on the DVD player but process A needs the DVD player to install a driver). In some cases, process A must "sit" and wait for process B to free the needed resource. In some other cases, process A may want to "steel" or grab the resource even if process B has not finished using it. To prevent a process A from accessing a resource that is not available, process B can (or must) lock the resource.

The columns produced by the sp_who stored procedure are:

spid: Each row in this column shows a unique integer (of type smallint) that identifies a process. The number is called a session identifier (or session ID). Notice that the first consecutive numbers are assigned to the sa account, the primary user of a Microsoft SQL Server installation
ecid: Like spid, the ecid (which stands for execution context ID) column has to do with the operating system's way of allocating threads to the different applications on the computer. Each row of this column assigns an integer (also of type smallint) to the corresponding value of the spid column
status: This column specifies what action is currently associated with the process in the corresponding spid column. Each action has a specific name:
- dormant: Right now, the process associated with the corresponding spid is not currently performing any actual action, for any reason
- running: The process is currently doing something
- background: The process is doing something behind-the-scenes, usually on behalf of one ore more other processes
- rollback: The process must restart a task
- pending: The process must wait, probably for another task
- runnable: The process is ready to perform its action
- spinloop: The process must wait for a locked resource to become free
- suspended: The process must wait for an intermediate action to complete, before performing its assignment
loginame: This is the user name of the person using the computer that owns the spid number
hostname: This is the name of the computer that the user with loginame is using
blk: If the session of the spid was blocked, the value of the blk column shows it. Otherwise, it displays 0
dbname: This is the name of the database the user is currently using
cmd: This is the name of the last SQL command the user had issued
request_id: This column shows an identifier, as an integer, for a request made to the process

As mentioned already, sp_who takes one argument. The value of the argument can be:

A Login Name: If you pass a login name, the database engine would check the status of the associated user:
- If you pass a login name that cannot be found in Microsoft SQL Server (maybe an unknown employee or an employee whose login name has not yet been created), you would receive an error. Here is an example:
- If you pass a valid login name, the database engine would check the status. If that user is not currently logged in, the results would be empty. Here is an example:
- If you pass a valid login name and that user is currently logged in, the procedure would show the status of that account. Here is an example:
Session ID: You can pass a session ID as argument, as a small integer:
- If you pass a number that is not associated with a process, the result would be empty. Here is an example:
- If you pass a number that was assigned to a process, the result would show the status of that session. Here is an example:
Active: If you pass the argument as ACTIVE, the procedure would show all session IDs that are currently active. Here is an example:

sp_who Stored Procedure

Database-Related Stored Procedures

Renaming an Object

To give you the ability to rename an object in a database, Transact-SQL provides a stored procedure named sp_columns. Its syntax is:

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' 
    [ , [ @objtype = ] 'object_type' ]

This procedure can be used to change the name of a database, a table, a view, a column, an index, a Transact-SQL type-defined data type, or a CLR type-defined data type.

This stored procedure takes two required and one optional argument. The first argument specifies the current name of the object you want to rename. The second argument specifies the new name that will be given to the object. The third argument is not required but it is (highly) recommended. Since this argument is optional, you can omit it. Here is an example:

USE Exercise1;
GO
sp_rename  N'Houses', N'Properties';
GO

If you omit this argument, the database engine would look for an object that holds the first argument's name. For example, if it finds a table or a view, it would change its name to that of the second argument. Therefore, to rename a table or a view, pass the first argument as the current name of the table and omit the third argument. To rename a database, pass the third argument as DATABASE. Here is an example:

USE master;
GO
sp_rename  N'Exercise', N'Example', N'DATABASE';
GO

To rename a column, pass the first argument as TableName.ColumnName, the second argument as the new name of the column, and the third argument as COLUMN.

Getting Information About a Database

To let you get as much information as possible about a database, Transact-SQL provides the sp_helpdb stored procedure. Its syntax is:

sp_helpdb [ [ @dbname= ] 'name' ]

This procedure takes one argument, which is optional. If you call it without an argument, the procedure would show you, among other things, the names of the databases, their sizes, and the dates they were created. Here is an example:

sp_helpdb Stored Procedure

As an alternative, to get information about a specific database, pass it as argument. Here is an example:

sp_helpdb Stored Procedure

Getting Information About the File Groups of a Database

Besides sp_helpdb, Transact-SQL provides the sp_helpfile stored procedure that produces information about a database. The syntax of the sp_helpfile stored procedure is:

sp_helpfile [ [ @filename= ] 'name' ]

Like sp_helpdb, sp_helpfile takes one optional argument. We saw that if you call the sp_helpdb stored procedure without an argument, the database engine would consider all database of the server. By contrast, if you call sp_helpfile without an argument, the database engine would find what the current database is. If you had not previously selected a database, then the master database is the current one. Here is an example:

sp_helpfile Stored Procedure

Otherwise, if you want to get information about a particular database, you can first select it:

sp_helpfile Stored Procedure

As an alternative, you can pass the name of the database or the name of one of its files as argument. Here is an example:

Getting Information About the File Group of a Database

To let you get some information about the file groups of a database, Transact-SQL provides the sp_helpfilegroup stored procedure. Its syntax is:

sp_helpfilegroup [ [ @filegroupname = ] 'name' ]

This stored procedure takes an optional argument. Here is an example of calling it without an argument:

sp_helpfilegroup Stored Procedure

If you pass the argument as 'primary', you would get the location of the main file group. Here is an example:

Tables and View-Related Stored Procedures

Getting Information About an Object

To get information about an object of a database, you can execute the sp_help stored procedure. Its syntax is:

sp_help [ [ @objname = ] 'name' ]

This stored procedure takes one optional argument. If you call it without passing an argument, it produces information about the database that is currently selected. This means that you should first specify a database before executing this procedure. Here is an example:

USE Exercise;
GO
sp_help;
GO

To get information about a specific object, pass its name as argument. Here is an example:

USE Exercise1;
GO
sp_help N'Employees';
GO

Getting the Size of an Object

The sp_spaceused stored procedure allows you to know how much memory a database or one of its objects is occupying. The syntax of this procedure is:

sp_spaceused [[ @objname= ] 'objname' ] 
             [,[ @updateusage= ] 'updateusage' ]

This procedure takes two optional arguments. If you execute it without an argument, you would get the different amounts of memory space the current database is using. To specify the database whose size you want to check, you should first select it. Here is an example:

sp_spaceused Stored Procedure

To know the space that a particular object is using, pass it as argument. Here is an example:

sp_spaceused Stored Procedure

Getting Information About the Columns of a Table

To get details about the columns of a table, you can execute the sp_columns stored procedure. Its syntax is:

sp_columns [ @table_name = ] object  [ , [ @table_owner = ] owner ] 
     [ , [ @table_qualifier = ] qualifier ] 
     [ , [ @column_name = ] column ] 
     [ , [ @ODBCVer = ] ODBCVer ]

This procedure can take many arguments but one is required. The required argument is the name of the table whose columns you want to investigate. Here is an example:

USE Exercise1;
GO
sp_columns  N'Employees';
GO

Refreshing a View

The sp_refreshview stored procedure allows you to update the metadata of a view. The syntax of this procedure is:

sp_refreshview [ @viewname= ] 'viewname'

Here is an example that executes this procedure:

USE Exercise;
GO
sp_refreshview N'People';
GO

Getting Information About a Trigger

To let you get information about a trigger, Transact-SQL provides a stored procedure named sp_helptrigger. Its syntax is:

sp_helptrigger [ @tabname= ] 'table' 
     	       [ , [ @triggertype = ] 'type' ]

Deleting an Alias Data Type

Imagine you had created a custom data type for your database:

USE Exercise;
GO
CREATE TYPE NaturalNumber FROM int;
GO

If you don't need such a data type any more, to assist you with removing it, Transact-SQL provides the sp_droptype. Its syntax is:

sp_droptype [ @typename= ] 'type'

This procedure takes one argument as the name of the custom data type you want to delete. Here is an example of executing it:

sp_droptype NaturalNumber;
GO

Showing the List of Constraints of an Object

Imagine you had created a database and added some constraints to it. Here are examples:

USE master;
GO
CREATE DATABASE Exercise1;
GO
USE Exercise1;
GO
CREATE TABLE Genders
(
    GenderID int identity(1, 1) not null,
    Gender nvarchar(20) not null,
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);
GO
CREATE TABLE Employees
(
    PersonID int identity(1, 1) not null,
    FirstName nvarchar(22) null,
    LastName nvarchar(22) not null,
    GenderID int
		CONSTRAINT FK_Genders FOREIGN KEY REFERENCES Genders(GenderID),
    HourlySalary money,
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID),
    CONSTRAINT CK_HourlySalary CHECK (HourlySalary > 12.20),
    CONSTRAINT CK_Gender CHECK (GenderID BETWEEN 0 AND 4)
);
GO

To let you get information about the constraints in a database, Transact-SQL provides the sp_helpconstraint stored procedure. Its syntax is:

sp_helpconstraint [ @objname= ] 'table' 
     [ , [ @nomsg= ] 'no_message' ]

This procedure takes an argument as the name of the object whose constraints you want to find out. The argument can be the name of a table. Here is an example:

As you can see, this procedure produces all constraint reference, both the primary key and foreign key(s). If a table contains many constraints, the database creates a summary. Here is an example:

Automatically Executing a Stored Procedure

A stored procedure usually shows its result only when it executes. Sometimes, you wish to automatically execute it at the time of your choosing. This is possible using the sp_procoption built-in stored procedure. Its syntax is:

sp_procoption [ @ProcName = ] 'procedure' 
    , [ @OptionName = ] 'option' 
    , [ @OptionValue = ] 'value'

Setting the Order of Triggers

The sp_settriggerorder built-in stored procedure allows you to specify the order by which your AFTER triggers should/must execute. Its syntax is:

sp_settriggerorder [ @triggername= ] '[ triggerschema. ] triggername' 
    , [ @order= ] 'value' 
    , [ @stmttype= ] 'statement_type' 
    [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

Sending Email

To give you the ability to send an email from a database, Transact-SQL provides the sp_send_dbmail stored procedure. This store procedure is created in the msdb database. This means that you must reference it when executing this procedure. It's syntax is:

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
    [ , [ @subject = ] 'subject' ] 
    [ , [ @body = ] 'body' ] 
    [ , [ @body_format = ] 'body_format' ]
    [ , [ @importance = ] 'importance' ]
    [ , [ @sensitivity = ] 'sensitivity' ]
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
    [ , [ @query = ] 'query' ]
    [ , [ @execute_query_database = ] 'execute_query_database' ]
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
    [ , [ @query_attachment_filename = ] query_attachment_filename ]
    [ , [ @query_result_header = ] query_result_header ]
    [ , [ @query_result_width = ] query_result_width ]
    [ , [ @query_result_separator = ] 'query_result_separator' ]
    [ , [ @exclude_query_output = ] exclude_query_output ]
    [ , [ @append_query_error = ] append_query_error ]
    [ , [ @query_no_truncate = ] query_no_truncate ]
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

As you may guess, most of the arguments are optional. Otherwise:

The profile_name value must hold a valid for an existing profile. If you omit this argument, the database engine uses the current user or a default profile created in the msdb database. If there is no default profile, you must create one. To do this, in the Object Explorer, expand the Management node and double-click Database Mail. A wizard would start:

Click Next. If you are trying a new profile, click the first radio button:

Click Next. This would bring the third page of the wizard:

In the Profile Name text box, enter the desired name. If you want, type some explanation in the Description text box. To specify an account that can send emails, click Add and fill out the form. Once you are ready, click OK. If you want to add another profile, click Add again, fill out the form, and click OK. On the third page of the wizard, click Next twice. When the wizard has finished, click Close.

The recipients value represents one or more email addresses that will receive the message. If you want to send the message to only one recipient, provide the address. To get the message to more than one recipient, separate their email addresses with commas
The copy_recipient value represents one or more email addresses that will receive a copy of the message. This is equivalent to cc. To send the message to one recipient, provide the address. To send the message to many recipients, provide the necessary email addresses separated by commas
The blind_copy_recipient value follows the same rules as the copy_recipient argument except that the recipients will not see each other's email address. This is equivalent to bcc
The subject value is the subject of the message
The body value is the actual message to send
The body_format value specifies the format to use
The importance value specifies the level of importance of the message
The sensitivity value specifies the type of sensitivity of the message

Here is an example of executing this stored procedure:

USE Exercise;
GO

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = N'Central Administrator',
	@recipients = N'jaywiler@hothothot.net',
	@body = N'The Persons table has received a new record.',
	@subject = N'New Record';
GO

Before executing this procedure, you should check the security settings on your server. Otherwise you may receive an error. Here is an example:

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database 
Mail XPs' because this component is turned off as part of the security configuration 
for this server. A system administrator can enable the use of 'Database Mail XPs' 
by using sp_configure. For more information about enabling 'Database Mail XPs', 
see "Surface Area Configuration" in SQL Server Books Online.

To solve this problem, open a Query window and type the following:

sp_configure N'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure N'Database Mail XPs', 1;
GO
RECONFIGURE;
GO