sp_help, sp_helptext and sp_depends are three very useful system stored procedures in SQL Server. These three system stored procedures are frequently used by developers.
sp_help : This will show the information about the database object like parameter's name, their data types etc. We can use sp_help with any database objects like stored procedures, tables, views, triggers etc.
sp_help : This will show the information about the database object like parameter's name, their data types etc. We can use sp_help with any database objects like stored procedures, tables, views, triggers etc.
sp_helptext : This will show the text of the database objects like stored procedures, views etc.
sp_depends : This system stored procedure is used to see the dependencies of the database objects like stored procedures, tables etc. It is very useful specially if you want to check if there are any stored procedures, views or triggers that are referencing table which you are about to drop.
I have created a table and a stored procedure to show how the three system stored procedures work.
USE [AdventureWorks]
GO
-- Create table tbl_temp
CREATE TABLE tbl_temp(
[code] [int] NOT NULL,
[type] [nvarchar](50) NULL,
[quantity] [int] NULL
)
GO
-- Insert data in the table
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (1, N'Radio', 17)
GO
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (2, N'Mobile', 22)
GO
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (3, N'TV', 31)
GO
-- Create stored procedure
spCount_tbl_temp
CREATE PROCEDURE spCount_tbl_temp
@typename NVARCHAR(20)
AS
BEGIN
SELECT quantity FROM tbl_temp
WHERE [type]=@typename
END
-- System stored procedure sp_help
EXEC sp_help spCount_tbl_temp
-- System stored procedure sp_helptext
EXEC sp_helptext spCount_tbl_temp
-- System stored procedure sp_depends
EXEC sp_depends spCount_tbl_temp
