Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

Write for Us

Share you SQL Server knowledge with others and raise your profile in the community More...

Create and Manage SQL Server Stored Procedures using Transact-SQL

By : Randy Dyess
Jun 29, 2002

Page 2 / 4

Now that we have a stored procedure built in SQL Server, or a extended stored procedure added to the master database, we have several ways to return information about that stored procedure to our users or to our code.

Determine the Stored Procedures or Extended Stored Procedures Currently in the Database

SP_STORED_PROCEDURES
System stored procedure which returns a list of stored procedures in the current environment.

Syntax
sp_stored_procedures [[@sp_name =] 'name'] 
    [,[@sp_owner =] 'owner'] 
    [,[@sp_qualifier =] 'qualifier']

SP_HELPEXTENDEDPROC
System stored procedure that displays the currently defined extended stored procedures and the name of the dynamic-link library to which the procedure belongs too.

Syntax
sp_helpextendedproc @funcname = 'procedure'

Determine the ID Number of the Stored Procedure

@@PROCID
Metadata function returning an INTEGER representing the current stored procedure's identifier number.

Syntax
@@PROCID

OBJECT_ID
Metadata function that returns an INTEGER representing the identification number for the given object name.

Syntax
OBJECT_ID ('object')

Determine the Name of a Stored Procedure Given the ID Number

OBJECT_NAME
Metadata function that returns a NCHAR representing the database name for the given object identification number.

Syntax
OBJECT_NAME (object_id)

Determine an Object Dependencies

SP_DEPENDS
System stored procedure which displays information about database object dependencies.

Syntax
sp_depends [ @objname = ] 'object'

Return Properties, Columns, Parameters and the Text of a Stored Procedure

OBJECT_PROPERTY
Metadata function that will return an INTEGER representing information about a specified object in the current database.

Syntax
OBJECTPROPERTY (id, 'property')

SP_SPROC_COLUMNS
System stored procedure which returns column information for a specified stored procedure or user-defined function.

Syntax
sp_sproc_columns [[@procedure_name =] 'name'] 
    [,[@procedure_owner =] 'owner'] 
    [,[@procedure_qualifier =] 'qualifier'] 
    [,[@column_name =] 'column_name']
    [,[@ODBCVer =] 'ODBCVer']

COLUMNPROPERTY
Metadata function that returns an INTEGER representing information about a column in a stored procedure or table given the stored procedure or table ID, the column name and the property of the column you are asking about.

Syntax
COLUMNPROPERTY ( id , 'column' , ' property' )

SP_HELPTEXT
System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.

Syntax
sp_helptext @objname = 'name'

INFOMATION_SCHEMA.PARAMETERS
Informational schema view located in the master database (SQL Server 2000) or all databases (SQL Server 7.0) containing one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.

We now know how to return information about our stored procedures, let's look at the objects that allow us to change a stored procedure's text and basic options, or change how SQL Server sees the a stored procedure.

<< Prev Page Next Page>>