How to create a database cleanup report in SQL Server

A SQL Server database can have stored procedures, tables, defaults, views etc. that aren’t being used anymore, and unless you determine which of your objects are truly unreferenced you will be stuck with them, or risk breaking your database if you delete a wrong object. This is where ApexSQL Clean can help. ApexSQL Clean’s main features are already described in SQL code analysis – full body scan of a SQL database, but here we would like to point out and present one more useful feature: creating a sql database cleanup report. In order to have a complete database cleanup report and dependencies analysis procedure, ApexSQL Clean has a reporting mechanism, which allows keeping track of selected objects and their dependencies.

Creating a report will provide you with detailed information, whether you want to include objects that have dependent objects and their relationship (parent – children), or you need to create a document containing objects that have no children objects or other references and can be safely removed. In the ApexSQL Clean’s main grid they are marked as Unreferenced, and are selected by default:

Now, let’s take a look at the report creation process. To create a report for the selected objects, you should use the Export command located on the main toolbar, in the Actions section:

As shown in the illustration above, there are two export options available: HTML report and XML report. If we select either option, a new window will open and provide the following customization options:

The generated sql database cleanup report will include all the previously selected information and create a report allowing you to easily print out a hard copy of it, if necessary. The report is a well-organized, table based document:

Here we included all the available options, but in case of unreferenced objects, some options like Count of database references or Count of file references, may be excluded from the report in order to have a more compact preview by removing superfluous columns.

When it comes to the XML report, the generated file will look something like this:

XML format files are self-describing, humanly readable, easy to create and extend. The XML encoding clearly describes the data types and data elements of the data file. The advantage of XML files is that they can be used to bulk import data into SQL tables with INSERT INTO and SELECT * FROM OPENROWSET(BULK…) statements or otherwise post-processed with custom applications.

For example, here’s how to read the data like Type, Relationship, Name and Dependent object from the above illustrated XML report using a SQL query:

DECLARE @Xml TABLE (XmlData XML);

INSERT INTO @Xml (XmlData)
SELECT *
FROM OPENROWSET(BULK 'c:SamplesReport.xml', SINGLE_BLOB) O;

SELECT [Row].value('Type[1]', 'Varchar(255)') AS Type,
 [Row].value('Relationship[1]', 'Varchar(255)') AS Relationship,
 [Row].value('Name[1]', 'Varchar(255)') AS NAME,
 [Row].value('Columns[1]/Column[1]/Name[1]', 'Varchar(255)') AS 'Dependent Object'
FROM @Xml
CROSS APPLY XmlData.nodes('/ApexSQLClean/References/Object/Reference') AS [Table]([Row])

After executing it, the results are:

Using the report feature in ApexSQL Clean allows generating a precise document containing all the information about objects, types, their dependencies, etc. whether you need to report the referenced objects, or to keep a record of the removed – unreferenced ones.