Several system stored procedures provide information about stored procedures with system tables. Use these stored procedures:
View the Transact-SQL statement used to create a stored procedure. This is useful for users who do not have a Transact-SQL script file that creates a stored procedure. Get information about the stored procedure (such as the owner of the stored procedure, create time, and its parameters). List the objects used by the specified stored procedure and using the specified stored procedure. This information can be used to identify the processes that are affected by changes or delete an object in a database.
View the definition of the stored procedure
SP_HELPText
Display rules, default, uncrypted stored procedures, user-defined texts of functions, triggers, or views.
grammar
sp_helptext [@objname =] 'name'
parameter
[@ObjName =] 'name'
The name of the object will display the definition information of the object. Objects must be in the current database. Name's data type is NVARCHAR (776) without default.
Returns the code value
0 (success) or 1 (failed)
Result set
Column Name Data Type Description TextnvarChar (255) Object Defining Text
Comment
SP_HELPText displays the text used to create an object in multiple rows, where each line has 255 characters defined by Transact-SQL. These definitions only reside in the text of the SysComments table of the current database.
Authority
Perform permissions The default is granted public roles.
Example
The following example shows the text of the Employee_insUpd trigger, which in the database PUBS.
USE PUBS
EXEC SP_HELPTEXT 'EMPLOYEE_INSUPD'
View information about stored procedures sp_help
Reports Database objects (any objects listed in the sysObjects table), user-defining information of data types or data types provided by Microsoft® SQL ServerTM.
grammar
sp_help [[@ObjName =] name]
parameter
[@ObjName =] Name
It is the name of any object in sysObjects, or the name of any user defines the data type in the SYSTYPES table. Name's data type is NVARCHAR (776), the default value is NULL. Can't use the database name.
Returns the code value
0 (success) or 1 (failed)
Result set
The returned result set depends on whether Name is specified, when specified and what database objects are there.
If SP_HELP without parameters is performed, return the summary information of all types of objects in the current database.
Column Data Type Description Namenvarchar (128) Object Name Ownernvarchar (128) Object Owner Object_TypenVarchar (31) Object Type If the Name is a SQL Server data type or a user-defined data type, sp_help returns this result set.
Column Name Data Type Description 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). Scalein's number of digits on the right side. Nullablevarchar (35) indicates whether a null value is allowed: Yes or no. DEFAULT_NAMENVARCHAR (128) Binds the default name of this type. If there is no bound default value, null is NULL. Rule_namenvarchar (128) Binds the rule name of this type. If there is no bound default value, null is NULL. COLLATIONSYSNAME Data Type Sort Rules. If it is a non-character data type, null is NULL. If Name is any database object (instead of data type), SP_HELP will return this result set, as well as other result sets based on the specified object type. Column Data Type Description NamenvarChar (128) Table Name Ownernvarchar (128) Table of the Typenvarchar (31) table of the type created_datetimeDateTime created date table, sp_help returns other result sets according to the specified database object. If Name is a system table, a user table or view, sp_help returns these result sets (exception, for the view, does not return the result set of the data file in the file group).
Return to other result set by list:
Column Data Type Description Column_namenvarchar (128) column name. Typenvarchar (128) column data type. Computevarchar (35) indicates whether the value in the column is calculated: (yen). Lengthint is the length of the byte unit. PRECCHAR (5) column accuracy. ScaleChar (5) Column numerical range. Nullablevarchar (35) indicates whether the NULL value is allowed in the column: Yes or no. TrimtrailingBlanksvarchar (35) Tailing the tail with spitral (yen). FixedlennullinsourceVarchar (35) is just backward compatible. Coll rules of collationsysName columns. If it is a non-character data type, null is NULL. Other result sets returned by identity column:
Column Name Data Type Description IdentityNVARCHAR (128) The data type is declared as the name of the identity. SEEDNUMERIC identity the starting value of the column. IncrementNumeric This value is used in this column. NOT for Replicationint When you repeatedly log in (for example, SQLREPL) Attempts to insert data in the table, you cannot force the Identity property: 1 = true0 = false Return to other result sets:
Column Name Data Type Description RowGuidColsysName The name of the global unique identifier column. Other result sets returned by the file group:
Column Data Type Description Data_located_on_filegroupnvarchar (128) The file group (main file group, secondary file group, or transaction log). Other result sets returned by index:
Column Name Data Type Description Index_NameSysName Index Name. Description of Index_Descriptionvarchar (210) index. Index_Keysnvarchar (2078) Generates the column name of the index. Other result sets returned by constraint
Column Data Type Description Constrain_typenvarchar (146) constraint. Constrain_namenvarchar (128) Constraint. DELETE_ACTIONNVARCHAR (9) Indicates that the delete operation is: no operation, lamination, or temporary. (I only apply to Foreign Key constraints.) Update_actionnvarchar (9) indicates that the UPDATE operation is: no operation, lamination, or temporary. (Applicable only for Foreign Key constraints.) Status_enabledvarchar (8) indicates whether constraints are enabled: enabled, disabled or tired. (Applicable only to Check and Foreign Key constraints.) Status_for_replicationvarchar (19) specifies whether the constraint is used to copy. (Only for Check and Foreign Key constraints.) Constrain_keysnvarchar (2078) constitutes the column name of the constraint. Or (for default and rules) specify the text of the default or rule. Other result sets returned by reference object: Column Name Data Type Description Table is Reference Bynvarchar (516) Identify other database objects of the reference table. If Name is a system stored procedure or an extended stored procedure, SP_HELP will return this result set.
Column Data Type Description Parameter_namenvarchar (128) Stored Procedure Parameter Name. TypenVarchar (128) The data type of the process parameter is stored. Lengthsmallint maximum physical storage length (in bytes). Precint precision (total number of digits). ScaleInt number of numbers on the right side of the decimal point. The order of parametersmallint parameters.
Comment
The sp_help process looks for objects only in the current database.
When no Name is specified, sp_helptrigger lists the names, owners, and object types of all objects in the current database. SP_HELPTRIGGER provides information about the trigger.
Authority
Perform permissions The default is granted public roles.
Example
A. Return information about all objects
The following example lists information about each object in sysObjects.
Use master
EXEC SP_HELP
B. Return information about a single object
The following example shows information about the Publishers table.
USE PUBS
EXEC SP_HELP PUBLISHERS
View the relevant database of stored procedures sp_depends
Displays information about database object-dependent (for example, views and processes of tables or views, and tables and views depend on). Non-report references to objects outside the current database. Syntax sp_depends [@objName =] 'Object' parameter [@objName =] 'Object' Check the relevant database object. Objects can be tables, views, stored procedures, or triggers. Object's data type is VARCHAR (776), without default. Returns the code value 0 (success) or 1 (failed) result set sp_depends displays two result sets. The following result set displays the objects dependent on Object.
Column Data Type Description NamenvarChar (40) There is a relevance project name. Typenvarchar (16) Project Type. Updatednvarchar (7) Do you update the project. SELECTEDNVARCHAR (8) The project is used for the SELECT statement. ColumnSysName There is a column or parameter in association. The following result set shows the object that relies on Object.
Column Data Type Description NamenvarChar (40) There is a relevance project name. Typenvarchar (16) Project Type. Note If an object references another object, it is considered that the former relies on the latter. Sp_depends determines dependencies by viewing the sysdepends table. Permissions Permissions Default Grant Public Roles. Example The following example lists the database objects depending on the Customers table. Use northwindexec sp_depends 'Customers' View information about the extended stored procedure sp_helpextendedProc displays the currently defined extension stored procedure, as well as the name of the dynamic link library of this process (function). Syntax sp_helpextendedProc [[@funcname =] 'procedure'] parameter [@funcname =] 'procedure' is the name of the extended stored procedure to display its information. Procedure's data type is sysname, the default value is NULL. Returns the code value 0 (success) or 1 (failed) result set
Column Data Type Description NamesysName The name of the extension stored procedure. Dllnvarchar (255) The name of the dynamic link library. Note Specify Procedure, sp_helpextendedProc reports information that specifies the extension stored procedure. When you do not provide Procedure, sp_helpextendedProc returns the name of all expansion stored procedures, and the DLL name to each extended stored procedure. Permissions Permissions Default Grant Public Roles. Example A. Report all the examples of all extended stored procedures Report all the information of all extended stored procedures. Use master
EXEC SP_HELPEXTENDEDPROC
B. Report a single extension stored procedure Help The following example report XP_cmdshell extension stored procedures. Use master
EXEC SP_HELPEXTENDEDPROC XP_CMDSHELL is from: http://goarer.xicp.net/showlog.asp?id=517
