It is very tedious work to identify unused and unreferenced objects in a database from the [master].[dbo].[sysdepends] and [master].[dbo].[sysobjects] system tables. (Woe to the manager who assigned you this task.) Even a small database will take a considerable amount of time because you cannot rely on the [master].[dbo].[sysdepends] and [master].[dbo].[sysobjects] system tables. They do not always produce correct results. That means you cannot really rely on SQL Server’s mechanism for finding the dependent objects.
What is the significance of removing unreferenced objects?
There’s no rule or restriction that says you have to remove unreferenced objects; hence, this is not a technical issue. But since your database can have as many as 2,147,483,647 objects, it is an issue of maintainability.
Nowadays we segregate our routine work and rotate it with other DBAs. So it is important that a database be easily maintainable by other DBAs. For instance, they will need to know which applications use which objects and whether they have any dependencies in the current database or another database. When another DBA not as familiar with the database as you are finds issues to resolve, trying to find the unused or unreferenced objects so they can remove them is going to take hours of work. This is where ApexSQL Clean can help. In this spotlight we are going to see how.
|
Software Spotlight |
|
|
Product |
ApexSQL Clean |
|
Version |
2005.07 |
|
Publisher |
ApexSQL LLC. |
|
Price |
$199 / $239 with 1-year maintenance |
System Requirements
- Operating System: Windows XP Professional + SP2
- Ram: 1.5 GB
- CPU: Intel Pentium P4 2.6 GHz genuine
- Hard Disk: 120 GB @ 7200 RPM
Main Features
The main feature of ApexSQL Clean, of course, is that it finds unreferenced objects, but it has some other exciting features too:
- Supports SQL Server 2000 (32-bit), SQL Server 2005 (32-bit) and SQL Server 7.0.
- Supports wildcards (like “*” and “?”) in filters.
- Generates a drop script for unreferenced objects.
- Can backup a database before dropping an object.
- Uses its own proprietary method of searching for referenced objects, which does not rely on system tables.
- Generates more-accurate results.
- Includes a command line utility.
Installation
Installation is very easy and self-explanatory.
When you are finished with the installation, you will find a simple GUI to get into the utility. It will be very familiar if you have used any of ApexSQL’s other tools.
Dependency Analysis Wizard
You can choose to create a new project or open or edit an existing one.
When selecting to create a new project you will be asked for the server, login credentials and the name of the database for which you are going to find unreferenced objects.
