Home

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:

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

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.

Database-Related Stored Procedures

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:

sp_helpfile Stored Procedure

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:

sp_helpfilegroup Stored Procedure

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
  Store Procedure: sp_help

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

Stored Procedure: sp_columns

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:

Stored Procedure: sp_constraints

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:

Stored Procedure: sp_constraints

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:

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 Editor and type the following:

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

Previous Copyright © 2011-2013 FunctionX Next