How to safely delete SQL database objects (4212863)

When trying to delete a database object, which has an object that depends on it, no problems can be caused, since it will not allow to be deleted:

Drop failed for Table 'HumanResources.Employee'. (Microsoft.SqlServer.Smo)
Could not drop object 'HumanResources.Employee' because it is referenced by a FOREIGN KEY constraint.

The situation is completely different when a database object that depends on other objects is deleted. There will be no warning messages. The problems might appear afterwards, when it’s realized that the specific object is actually still needed; it wouldn’t have been deleted it was known that it was dependent on other database objects, or that it is still used in external databases or other projects.

To be certain that no references will be broken when deleting a database object, check if there are other objects that depend on the one that will be deleted, and if there are any that it depends on.

To see object dependencies:

1. Open SQL Server Management Studio

2. Right click the object that is to be deleted and select the Delete option from the drop down list:

   

3. In the Delete object dialog, click the Show dependencies button:

   

4. Check the list of objects that depend on the selected object and the objects on which it depends:

   

   

The flaws of this method are:

• The objects have to be checked one-by-one. It’s a lot of time for a large number of database objects
• It doesn’t show all dependencies – such as references to objects in linked servers
• It doesn’t show if the object is used in any projects

Another method is to analyze SQL Server’s system tables in the master database:

1. The sysobjects table contains the list of all objects in your database:
2. The sysdepends table contains dependency information between objects in the database (views, procedures, and triggers), and the objects that are contained in their definition (tables, views, and procedures)
   Or use stored procedures:
   
3. Use the sp_depends stored procedure – it displays the information about database object dependencies. The biggest flaw is that the references to objects in external databases are not reported
4. For SQL Server 2008 and later, use sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities and sys.sql_expression_dependencies stored procedures. They return information for each entity in the current database that references another user-defined entity by name, each user-defined entity referenced by name in the definition of the specified referencing entity and each by-name dependency on a user-defined entity in the current database, respectively

The flaws are again the same – not all dependencies are shown and it’s time consuming.

ApexSQL Clean can help determine all dependencies in a database, dependencies with objects in an external database or .NET, Delphi, C++ and ASP projects. It also analyzes SQL Server database object dependencies, as well as the impact of potential changes and deletions on your SQL database, and determines object interrelationships within the database, between different databases, scripts and even applications.

To find all dependencies for an object:

1. Run ApexSQL Clean
2. In the Main window click the New button
3. Select the SQL database you want to connect to
4. If there are any external databases that need to be analyzed during the process, switch to the External databases tab and select them
5. Use the Object filter to limit the number of object types to be analyzed, which can speed up the process significantly:

   

6. When everything is set, click the OK button

   The object referential status and information will be displayed in the Results grid section. Unreferenced objects will be marked with a check sign in the Unreferenced column. Only these objects can be dropped from the database safely:

    

7. Use the Object filter in the left pane to remove the object types that will not be analyzed
8. In the Main grid, select the objects that will be dropped

9. Click the Create drop script option from the Home tab in the Actions group:

   

10. To execute the script from the script editor, click Execute or press F5

If a database needs to be cleared and objects that are not needed removed, ApexSQL Clean to make sure that no dependencies will be broken.