How to remove SQL Server database objects safely

ApexSQL Clean can help determine all dependencies in a SQL database; it also analyzes the impact of potential changes and deletions on SQL database, and determines object interrelationships within the database, between different databases, SQL scripts and even applications

When trying to remove SQL Server database objects, which have objects that depends on it, there shouldn’t be any problems because simply SQL Server won’t allow that, and the following warning message will be displayed:

But the situation is completely different when deleting a database object that depends on other objects, because there will be no warning messages. The problems might appear afterwards, when it became apparent that specific object is needed and it is still used in a database

Let’s check how ApexSQL Clean helps with removing SQL database objects safely

ApexSQL Clean will analyze a database to determine what SQL objects are actually being used and what objects aren’t; it will also find the internal references that exist among SQL objects thanks to its SQL database change impact analysis engine. In the main grid, unused objects, or objects that can safely be removed, rather, are marked as Unreferenced

Before describing further, let’s explain the terms Parent and Children object. The Parent object has other objects depending on it, while the Children are objects that depend on another objects

To define more precisely, the Unreferenced field in the main grid indicates whether the object is un-referenced (has no children) or is referenced (has children). So, this is where the object referential status and information will be displayed in; unreferenced objects will be marked with a check sign, and only these objects can be dropped from the database safely

What other information can we get about an unreferenced object?

ApexSQL Clean provides dependencies information located in the Dependencies preview window, in form of a tree-view. This is useful when a quick check-up of the parent objects is needed

Additional information on the object dependencies are easy to find in the Reference grids: Children and Parents, but since we are describing unreferenced objects only, let’s take a look at the second one, the Parents pane:

Here we can check the parent object that selected unreferenced object depends on, down to the column level, or even view its DDL script, if necessary

Now, if we proceed and click the Create drop script button, once we decide what objects are not needed, ApexSQL Clean will summarize the results and create a list

The last thing to do is to select output options. There are multiple choices here, whether we want to create a drop script, execute it, save it to file, or open it immediately in an editor

Here’s an example of the resulting drop script for one single unreferenced object, in this case the function [dbo].[ufnGetContactInformation]:

USE [AdventureWorks2012]
GO
SET NOEXEC OFF
SET IMPLICIT_TRANSACTIONS OFF
GO

BEGIN TRANSACTION
GO

-- DROP FUNCTION [dbo].[ufnGetContactInformation]
DROP FUNCTION [dbo].[ufnGetContactInformation]
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 
BEGIN
 PRINT 'Error Occurred when dropping FUNCTION: [dbo].[ufnGetContactInformation]'
 IF @@TRANCOUNT>0
  BEGIN
   ROLLBACK
  END
 SET NOEXEC ON
END
ELSE PRINT 'FUNCTION [dbo].[ufnGetContactInformation] DROPPED'

GO

IF @@TRANCOUNT>0
COMMIT
GO

SET NOEXEC OFF
GO

If there is a need to clean SQL database objects safely, ApexSQL Clean will help with this task while making sure that no dependencies are broken