Deleting Stored Procedures

If you no longer need a stored procedure, you should delete it so that you free up space on the server.
As a SQL Server Administrator, deleting a user-defined stored procedure is a straightforward task, but it requires careful consideration and understanding of the implications. Here’s how to perform this operation:

1. Verify the Procedure's Existence and Usage: Before proceeding with the deletion, ensure that the stored procedure you intend to delete is correctly identified and is not in use. Removing a stored procedure that is actively used by applications can lead to system failures or data integrity issues.
2. Use the `DROP PROCEDURE` Statement: To delete a stored procedure, you use the `DROP PROCEDURE` statement followed by the procedure's name. The syntax is as follows:
   

   DROP PROCEDURE [schema_name.]procedure_name;
   

   
   Replace `schema_name` with the schema where the procedure resides, if applicable, and `procedure_name` with the name of the procedure you wish to delete.
3. Consider Permissions: Ensure that you have the necessary permissions to drop the procedure. Typically, this requires membership in the `db_owner` fixed database role or the `dbo` schema, or owning the procedure.
4. Execute the Statement: Run the `DROP PROCEDURE` statement in your SQL Server Management Studio (SSMS) or any other SQL Server interface you use. This will permanently remove the stored procedure from the database.
5. Check for Dependencies: Prior to dropping the procedure, it’s advisable to check for any dependencies. You can use the `sp_depends` stored procedure or the `sys.dm_sql_referencing_entities` dynamic management function to identify any objects that depend on the procedure you plan to delete.
6. Backup Considerations: Consider taking a backup of the procedure's code, especially if it's complex or critical. This is useful if you need to restore or reference the procedure at a later date.
7. Log the Change: Document the deletion of the stored procedure, including the reason for its removal and the date of the operation. This helps in maintaining a record for future reference and auditing.

Example of deleting a stored procedure:

DROP PROCEDURE dbo.MyProcedure;

This statement will remove the stored procedure named `MyProcedure` from the `dbo` schema.
In summary, deleting a user-defined stored procedure in SQL Server involves verifying the procedure's existence and usage, ensuring proper permissions, using the `DROP PROCEDURE` statement, and considering backup and logging for record-keeping and recovery purposes.

To delete your stored procedure with Enterprise Manager:

1. Right-click the name of the stored procedure shown in Enterprise Manager.
2. Choose the Delete menu option.

DROP PROCEDURE usp_DeleteEmployee