{"id":18937,"date":"2022-11-11T07:26:00","date_gmt":"2022-11-11T06:26:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=18937"},"modified":"2022-12-01T09:25:14","modified_gmt":"2022-12-01T08:25:14","slug":"drop-sql-server-stored-procedures","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/drop-sql-server-stored-procedures\/","title":{"rendered":"Drop SQL Server stored procedures without errors"},"content":{"rendered":"\n

How to drop a SQL Server stored procedure without error using the existence check?<\/em><\/strong><\/h4>\n\n\n\n

To drop a SQL Server stored procedure and avoid errors, first test the existence of the procedure in the SQL Server database to condition the DROP PROCEDURE command. On the other hand, it is a good practice to always test the existence of SQL Server objects. In this case a stored procedure with the IF EXISTS command.<\/p>\n\n\n\n

1. Script to Drop a stored procedure<\/h2>\n\n\n\n

Here is the basic T-SQL command to delete a SQL Server stored procedure:<\/p>\n\n\n\n

DROP PROCEDURE dbo.uspGetClient;\nGO\n<\/pre>\n\n\n\n
2. DROP MS SQL procedures without error<\/h2>\n\n\n\n
First, how do you delete a SQL Server stored procedure without generating an error? That is, check if the object exists before deleting it with the SQL DROP<\/a> command. To do this, use the T-SQL IF EXISTS function. As in this example, on SQL Server versions prior to 2016.<\/p>\n\n\n\n
IF EXISTS (\n  SELECT name\n  FROM sysobjects\n  WHERE name = 'uspGetClient\n    AND type = 'P\n)\nDROP PROCEDURE dbo.uspGetClient;\nGO\n<\/pre>\n\n\n\n
Indeed, if the procedure does not exist the following error message is displayed, on English versions of SQL Server:<\/p>\n\n\n\n
Cannot drop the procedure ‘uspGetClient’, because it does not exist or you do not have permission.<\/em><\/p>\n\n\n\n
3. Existence tests integrated since SQL Server 2016<\/h2>\n\n\n\n
Note that since the SQL Server 2016 version, it is possible to test if a function, table or stored procedure exists and delete it with a single SQL command.<\/p>\n\n\n\n
DROP PROCEDURE IF EXISTS dbo.uspGetClient;\nGO\n<\/pre>\n\n\n\n
4. Generate a dynamic script to delete all stored procedures<\/h2>\n\n\n\n
Finally, here is a request to generate an DDL<\/a> script to delete all stored procedures from the database. The code uses the sys.procedures system view, stored in the msdb system database.<\/p>\n\n\n\n
SELECT\n  'DROP PROCEDURE IF EXISTS ' + SCHEMA_NAME(schema_id) + '.' + [Name] + ';' AS [SQL_Query], \n  SCHEMA_NAME(schema_id) AS [Schema_Name],\n  [object_id],\n  [type],\n  [type_desc],\n  [create_date]\nFROM sys.procedures\norder by 1;\n<\/pre>\n\n\n\n
The result is a column with one row for each stored procedure of user type. Each row contains a command to test the existence and delete the procedure. To use the code generated, two options are available.<\/p>\n\n\n\n
    \n
  1. The first one is to copy and paste the DROP PROCEDURE code generated and execute it in another SSMS Window.<\/li>\n\n\n\n
  2. The second is to add more code to automate the execution inside a T-SQL loop.<\/li>\n<\/ol>\n\n\n
    \n
    \"Generate
    Generate a dynamic script to Drop SQL Server stored procedure without errors<\/strong><\/figcaption><\/figure><\/div>\n\n\n
    \n
    How to create a SQL Server stored procedure ?<\/a><\/blockquote>