SQL Server Books Online: viewing stored procedures

server| Stored Procedures
Several system stored procedures provide information about stored procedures with the system tables. Use these stored procedures to: View Transact-SQL statements used to create stored procedures. This is useful for users who do not have Transact-SQL script files for creating stored procedures.
Obtain information about the stored procedure, such as the owner of the stored procedure, the time it was created, and its parameters.
Lists the objects used by the specified stored procedure and the procedure that uses the specified stored procedure. This information can be used to identify processes that are affected by changes or deletions to an object in the database. View the definition of a stored procedure sp_helptext
Displays the text of a rule, default value, unencrypted stored procedure, user-defined function, trigger, or view.
Grammar
sp_helptext [@objname =] ' name '
Parameters
[@objname =] ' Name '

The name of the object that will display the definition information for the object. The object must be in the current database. The data type of name is nvarchar (776) and there is no default value.
Return code value
0 (Success) or 1 (failed)
Result set
Column name data type description Textnvarchar (255) Object definition text

Comments
Sp_helptext Displays the text used to create objects in more than one row, each of which has 255 characters of Transact-SQL definitions. These definitions reside only in the text of the syscomments table in the current database.
Permissions
Execute permissions are granted to the public role by default.
Example
The following example displays the text of the EMPLOYEE_INSUPD trigger, which is in the database pubs.

Use pubsexec sp_helptext ' employee_insupd '



View information about stored procedures sp_help
Reports information about database objects (any objects listed in the sysobjects table), user-defined data types, or data types provided by Microsoft®sql Server™.
Grammar
sp_help [[@objname =] name]
Parameters
[@objname =] Name

is the name of any object in the sysobjects, or the name of any user-defined data type in the Systypes table. The data type for Name is nvarchar (776) and the default value is NULL. The database name cannot be used.
Return code value
0 (Success) or 1 (failed)
Result set
The result set returned depends on factors such as whether name is specified, when it is specified, and what database object it is. If you perform a sp_help with no parameters, the summary information for all existing type objects in the current database is returned. Column name data type description Namenvarchar (128) object name Ownernvarchar (128) object owner Object_typenvarchar (31) Object type
If name is a SQL Server data type or a user-defined data type, SP_HELP returns this result set. The column name data type describes the Type_namenvarchar (128) data type name. Storage_typenvarchar (128) SQL Server type name. The physical length, in bytes, of the Lengthsmallint data type. Precint Precision (total number of digits). Scaleint the number of digits to the right of the decimal point. Nullablevarchar (35) Indicates whether NULL values are allowed: Yes or No. Default_namenvarchar (128) is bound to the type's default value name. NULL if the default value is not bound. Rule_namenvarchar (128) is bound to the type's rule name. NULL if the default value is not bound. Collationsysname the collation of the data type. Null if it is a non-character data type.
If name is any database object (not a data type), then sp_help returns this result set and other result sets based on the specified object type. Column name data type description Namenvarchar (128) Table name Ownernvarchar (128) Table owner Typenvarchar (31) Table type Created_datetimedatetime created Date table

SP_HELP returns the other result set, based on the specified database object.

If name is a system table, user table, or view, then sp_help returns these result sets (except for the view, which does not return a result set that describes where the data file is located in the filegroup).
Returns other result sets by Column object: Column name data type description Column_namenvarchar (128) column name. Typenvarchar (128) column data type. Computedvarchar (35) indicates whether the value in the column is computed: (yes or no). Lengthint the length of the column in bytes. Precchar (5) Column precision. Scalechar (5) column value range. Nullablevarchar (35) Indicates whether NULL values are allowed in the column: Yes or No. Trimtrailingblanksvarchar (35) Trim trailing spaces (yes or no). Fixedlennullinsourcevarchar (35) is for backwards compatibility only. The collation of the Collationsysname column. Null if it is a non-character data type.
Other result sets returned by identity column: column name data type description Identitynvarchar (128) whose data type is declared as the identity of the column name. Seednumeric The starting value of the identity column. Incrementnumeric the increment used by the value in this column. Not for Replicationint The IDENTITY property cannot be enforced when repeated logons (for example, SQLREPL) attempt to insert data into a table:
1 = True
0 = False
Other result sets returned by column: Column name data type describes the name of the rowguidcolsysname globally unique identifier column.
Other result set returned by filegroup: Column name data type describes the filegroup in which the Data_located_on_filegroupnvarchar (128) data resides (primary filegroup, secondary filegroup, or transaction log).
Other result sets returned by index: column name data type description Index_namesysname index name. Description of the Index_descriptionvarchar (210) index. Index_keysnvarchar (2078) generates the column name of the column in which the index is located.
Other result set column name data types returned by constraint describe the type of the Constrain_typenvarchar (146) constraint. Constrain_namenvarchar (128) constraint name. Delete_actionnvarchar (9) indicates that the DELETE operation is: No action, cascade, or vacant.
(Applies only to FOREIGN KEY constraints.) ）
Update_actionnvarchar (9) indicates that the UPDATE operation is: No action, cascade, or vacant.
(Applies only to FOREIGN KEY constraints.) ）
Status_enabledvarchar (8) Indicates whether the constraint is enabled: enabled, disabled, or vacant. (For CHECK and FOREIGN KEY constraints only.) ) Status_for_replicationvarchar (19) Indicates whether the constraint is used for replication. (For CHECK and FOREIGN KEY constraints only.) ) Constrain_keysnvarchar (2078) is the name of the column that constitutes the constraint. or (for default values and rules) refers to the text that defines the default value or rule.
Other result sets returned by reference object: Column name data type description The table is referenced Bynvarchar (516) identifies other database objects that refer to tables.
If name is a system stored procedure or an extended stored procedure, then sp_help returns this result set. The column name data type describes the name of the Parameter_namenvarchar (128) stored procedure parameter. Typenvarchar (128) The data type of the stored procedure parameter. Lengthsmallint the maximum physical storage length, in bytes. Precint Precision (total number of digits). Scaleint the number of digits to the right of the decimal point. The order of the Param_ordersmallint parameters.
Comments
The sp_help procedure finds objects only in the current database.

When name is not specified, sp_helptrigger lists the names, owners, and object types of all objects in the current database. Sp_helptrigger provides information about triggers.
Permissions
Execute permissions are granted to the public role by default.
Example A. Returns information about all objects
The following example lists information about each object in sysobjects.

Use Masterexec sp_help
B. Returning information about a single object
The following example displays information about the publishers table.

Use pubsexec sp_help Publishers
To view the dependencies of a stored procedure
Sp_depends

Displays information about database object dependencies (for example, views and procedures that depend on tables or views, and tables and views on which the view or procedure depends).
References to objects outside the current database are not reported.
Grammar
sp_depends [@objname =] ' object '
Parameters
[@objname =] ' Object '

The database object that is checked for dependencies. An object can be a table, a view, a stored procedure, or a trigger. Object has a data type of varchar (776) and no default value.
Return code value
0 (Success) or 1 (failed)
Result set
Sp_depends displays two result sets.

The following result set shows the objects on which object is dependent.
The column name data type description Namenvarchar (40) has a related project name. Typenvarchar (16) project type. Updatednvarchar (7) Whether the item is updated. Whether the Selectednvarchar (8) item is used for a SELECT statement. Columnsysname a column or parameter that has a correlation.

The following result set shows objects that depend on object.
The column name data type description Namenvarchar (40) has a related project name. Typenvarchar (16) project type.
Comments
If an object references another object, the former is considered dependent on the latter. Sp_depends determines the dependencies by looking at the sysdepends table.
Permissions
Execute permissions are granted to the public role by default.
Example
The following example lists the database objects that depend on the Customers table.

Use northwindexec sp_depends ' Customers '


View information about an extended stored procedure

Sp_helpextendedproc
Displays the currently defined extended stored procedure and the name of the dynamic link library to which this procedure (function) belongs.
Grammar
Sp_helpextendedproc [[@funcname =] ' procedure ']
Parameters
[@funcname =] ' Procedure '

is the name of the extended stored procedure for which you want to display information. The procedure data type is sysname and the default value is NULL.
Return code value
0 (Success) or 1 (failed)
The result set column name data type describes the name of the namesysname extended stored procedure. Dllnvarchar (255) The name of the dynamic-link library.
Comments
When procedure is specified, SP_HELPEXTENDEDPROC reports the information that specifies the extended stored procedure. When no procedure is provided,
Sp_helpextendedproc returns the name of all extended stored procedures and the name of the DLL to which each extended stored procedure belongs.
Permissions
Execute permissions are granted to the public role by default.
Example A. Report help for all extended stored procedures
The following example reports information for all extended stored procedures.

Use Masterexec Sp_helpextendedproc
B. Reporting help for a single extended stored procedure
The following example reports the information for xp_cmdshell extended stored procedures.

Use Masterexec sp_helpextendedproc xp_cmdshell

Turn from: http://goaler.xicp.net/ShowLog.asp?ID=517