SQL Server Sp_help is part of database engine stored procedures ,which returns information about a database object or a data type.
Sp_help stored procedure is very helpful in many ways, as it returns all the information of database objects or data type so you can quickly check any table structure, number of columns are there in table, Indexes, constraints.
You can check function information. Also you can any data type information.
If you are looking for any information as mentioned above then sp_help command is the best way to get all these information.
SYNTAX
sp_help('objectname')
objectname is the name of any object, in sysobjects or any user-defined data type in the systypes table and it is nvarchar(776).
The default value for sp_help function is NULL.
Using sp_help to get information about all the objects in database
If you execute sp_help without any parameter then it returns information of objects of all types that exists in the current database.
Exec SP_HELP
As you can see, it returns the information of objects of all types that exist in the current database.
Using sp_help to get the information of table
Suppose, you want to see table EmployeeTerritories information then you need to pass table name as a parameter in sp_help stored procedure as shown below.
Exec SP_HELP EmployeeTerritories
As you can see, it returns complete information about table, in terms of table columns name with their data type and size along with the list of all foreign key and primary key created on table. If any identity is created on table or not , any index, and constraint on table or not and so on.
Using sp_help to get the information about data type
You can also check the data type information using sp_help.
Lets say, you want to see the information about data type VARCHAR, then you need to pass data type name as parameter in sp_help stored procedure as shown below.
As you can see, it returns information about data type varchar.
Using sp_help get the information about User Defined Function
Lets create a User Defined Function named Fn_Getsum
CREATE FUNCTION Fn_Getsum( @A Int , @B Int) RETURNS INT AS BEGIN RETURN (@A + @B) END
Once, you create the function in database, lets check the information about function using sp_help as shown below.
Exec sp_help Fn_GetSum
As you can see, it returns the complete information of function.
Using sp_help to get the information about stored procedure
First, we create a simple stored procedure named USP_GetSum
CREATE PROC USP_GetSum
@A INT,
@B INT
AS
BEGIN
SET NOCOUNT ON;
SELECT @A + @B AS SumofValues
END
Once, you create the Stored procedure, lets check the information about SP using sp_help as shown below.
Exec sp_help USP_GetSum
If you want to see the definition of objects you can use sp_helptext stored procedure.
