Home » SQL sp_help

SQL sp_help

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 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.

Then sp_help command is the best way to get answers of all these question in a easiest way.

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

 

sp_help

As you can see, it returns complete information of table, in terms of table columns name with their data type and size.

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 data type information using sp_help.

Lets say you want to see the information aboyut 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.

As you can see, it returns 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.

If you want to see the definition of objects you can use sp_helptext stored procedure.

Sp_helptext




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.