How to clean up unused objects from a SQL Server database

It is not a rare case that a DBA inherits SQL Server databases with many unused SQL objects. By using ApexSQL Clean, it is easy to clean a SQL database from these unneeded objects and prevent extra objects from making an impact on development by slowing it down and increasing the maintenance workload (e.g. all unused objects still have to have their permissions set, be conformant with coding standards, etc.)

In order to find unreferenced objects that should be cleaned from a SQL database:

  1. Run ApexSQL Clean
  2. Click the New button
  3. Select the SQL database
  4. If there are any external SQL databases that should be analyzed during the process, switch to the External databases tab and select these SQL databases

  5. Optionally, use the Object filter to limit the number of object types to be analyzed, which can speed up the process significantly
  6. After everything is set up, click OK

The object’s referential status and information will be displayed in the Results grid. Unreferenced SQL objects will be marked with a check sign in the Unreferenced column and only these can be selected to be cleaned from the SQL database.

Use the Object filter in the left pane to additionally eliminate unnecessary object types.

To clean a SQL database, select the objects to drop and click Create drop script on the Actions tab. The Drop script generation wizard will appear to allow selecting the SQL objects to be cleaned up. The objects selected before running the SQL script generation wizard will be checked by default

Regular expressions can be used in column row filters to show or hide SQL objects and again only unreferenced objects can be selected for dropping

ApexSQL Clean is a tool which can analyze the SQL Server database to find internal references that exist among SQL objects. Based on these analyses, ApexSQL Clean will generate a file of SQL drop statements to remove these useless SQL objects from a database. No need to mention how boring and time-consuming this cleaning process can be if performed manually. ApexSQL Clean will clean SQL Server database and save time and nerves and, at the same time, helps to avoid troubles and worries of breaking SQL databases.

April 2, 2015