Create and Manage SQL Server Stored Procedures using Transact-SQL


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

System stored procedure which returns a list of stored procedures in the current environment.
sp_stored_procedures [[@sp_name =] 'name']
    [,[@sp_owner =] 'owner']
    [,[@sp_qualifier =] 'qualifier']
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.

sp_helpextendedproc @funcname = ‘procedure’


Determine the ID Number of the Stored Procedure

Metadata function returning an INTEGER representing the current stored procedure’s identifier number.


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

OBJECT_ID (‘object’)


Determine the Name of a Stored Procedure Given the ID Number

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

OBJECT_NAME (object_id)


Determine an Object Dependencies

System stored procedure which displays information about database object dependencies.

sp_depends [ @objname = ] ‘object’


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

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

OBJECTPROPERTY (id, ‘property’)

System stored procedure which returns column information for a specified stored procedure or user-defined function.
sp_sproc_columns [[@procedure_name =] 'name']
    [,[@procedure_owner =] 'owner']
    [,[@procedure_qualifier =] 'qualifier']
    [,[@column_name =] 'column_name']
    [,[@ODBCVer =] 'ODBCVer']
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.

COLUMNPROPERTY ( id , ‘column’ , ‘ property’ )

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

sp_helptext @objname = ‘name’

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.


Pages: 1 2 3 4


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |