question

Bugmesh avatar image
Bugmesh asked

How do I remove orphaned stored procedures

I have found stored procedures that have broken references between sys.sysobjects and sys.modules. The object_id and object_name do not seem to match up on about 180 stored procedures in the database. I have checked for different schemas but that is not the case. Using a dbForge tool I was able to determine that all of these had an "invalid object name" reference (usually a table that does not exist.) My concern is that we will not be able to remove these since they are not consistent in the system views/tables.

I am not sure this rambling question makes any sense but, if you understand the issue I would appreciate any assistance you can provide.

stored-proceduresupdatedeleteobjectsorphan files
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Solo avatar image
Solo answered

You can use one of the following:

Using SQL Server Management Studio

To delete a procedure in Object Explorer

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.
  2. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
  3. Expand Stored Procedures, right-click the procedure to remove, and then click Delete.
  4. To view objects that depend on the procedure, click Show Dependencies.
  5. Confirm the correct procedure is selected, and then click OK.
  6. Remove references to the procedure from any dependent objects and scripts.

Using Transact-SQL

To delete a procedure in Query Editor

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.
  2. Expand Databases, expand the database in which the procedure belongs, or, from the tool bar, select the database from the list of available databases.
  3. On the File menu, click New Query.
  4. Obtain the name of stored procedure to remove in the current database. From Object Explorer, expand Programmability and then expand Stored Procedures. Alternatively, in the query editor, run the following statement.SQLCopy 
    SELECT name AS procedure_name   
    ,SCHEMA_NAME(schema_id) AS schema_name  
    ,type_desc  
    ,create_date  
    ,modify_date  
FROM sys.procedures;
  5. Copy and paste the following example into the query editor and insert a stored procedure name to delete from the current database.SQLCopy 
    ;  
GO  
" style="line-height: 19px; -webkit-font-smoothing: auto; font-family: monospace, monospace; font-size: 0.875rem; direction: ltr; border-radius: 2px; display: block; background-color: var(--body-background-medium); box-sizing: inherit;">DROP PROCEDURE <stored procedure name>;  
GO
  6. Remove references to the procedure from any dependent objects and scripts.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.