SQL Server - Different Types of Stored Procedure

Different Types of Stored Procedure

• User Defined Stored procedure: The user defined stored procedures are created by users and stored in the current database.
• System Stored Procedure: The system stored procedures have names prefixed with sp_. They are used to manage SQL Server administrative tasks. The system stored procedures are stored in the master and MSDB databases.
• Temporary Stored procedures: The temporary stored procedures’ names are prefixed with the # symbol. Temporary stored procedures are stored in the TEMPDB databases. These temporary procedures are automatically dropped when the connection terminates between client and server.
• Remote Stored Procedures: The remote stored procedures are created and stored in remote server databases. These remote procedures can be accessed by the users who have the appropriate permission.
• Extended Stored Procedures: These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_

Different Applications of Stored Procedures

1. To insert data
2. To update data
3. To delete data
4. To fetch Data
5. To search Data

Important System Stored Procedures

SP_HELP	Provides information about a database object, such as a table, view, or procedure.
SP_HELPINDEX	Displays information about the indexes on a table.
SP_HELPTEXT	Displays the text of a stored procedure, trigger, or user-defined function.
SP_RENAME	Renames a user-created object in the database.
SP_SPACEUSED	Displays the disk space used by a table, indexed view, or the whole database.
SP_DEPENDS	Displays information about the dependencies of a database object.
SP_RENAMEDB	Renames a database
SP_ADDUSER	Adds a new user to the database and assigns the user to a role.
SP_ADDROLEMEMBER
SP_WHO	Displays information about current users and processes connected to the database.