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
| 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.
Continues…
No comments yet... Be the first to leave a reply!