Finding Potentially Unused Views in Your Databases

If I were granted two ‘wishes’ for SQL Server meta-data they would be:

I assume that adding and populating a created column to sys.indexes wouldn’t be too hard to implement. It should just require a tiny bit of meta-data to be populated when initially creating indexes or statistics.

On the other hand, I’m guessing that keeping the data in a last_used column in sys.objects would add some overhead into ordinary options (such that it would probably make sense to only allow it to operate when a specific Trace Flag was put into play). This would be especially true if the purpose of this new column would be to keep the last_updated information truly up-to-date instead of using it to keep ‘rough’ details on when an object was last used.

That said, I’m also fairly confident that many DBAs would be happy to have such a feature built-in to the SQL Server engine – simply because it would provide a huge benefit when it comes to trying to track down non-used code (sprocs, views, udfs, and even triggers) as well as non-used tables and indexes.

Finding _POTENTIALLY_ Non-Used Objects in SQL Server

The internet is chock-full of different approaches to finding rarely/non-used code, tables, and indexes. Most of the approaches you’ll find to carry out these tasks online are creative – in that they have to work around the fact that there truly is no DEFINITIVE meta-data kept by SQL Server on when an object was last used.

And, of course, the idea is that if something hasn’t been used in say x days, or n months, then it’s probably safe to remove from production in most environments.

By and large, the approaches used to create lists of objects that haven’t been used or accessed in a while typically take one of two forms:

Sadly, neither approach can really provide a definitive list of non-used objects with any degree of certainty – which is why it’s best to think of the lists that these approaches provide as being a list of POTENTIALLY non-used objects.

Dealing with _POTENTIALLY_ non-used Objects

Which, in turn, is why all good advice that you’ll find online (or in print) about trying to find/remove non-used code and data in target databases always urges caution in removing objects that you think aren’t being used.

Because, while it’s tempting to want to just send objects ‘straight to hell’ by DROPing  them when you’re SURE they’re non-used, the reality is that some views, indexes, stored procedures, and so on might actually ONLY ever be used quarterly, bi-annually, yearly, or potentially even less-frequently. But their ‘sudden’ removal from a production system can really stymie reporting and other efforts.

As such, I typically advocate the following approach to gradually removing objects from a production system.

First, try renaming non-used objects. Many DBAs will simply rename non-used objects to zzzObjectName – which makes the ‘sort’ to the bottom of the list. This works well for tables and all other objects – as it keeps definitions and permissions intact. And, if you later find that you made a mistake, recovery is pretty easy – just remove the ‘zzz’ prefix and you’re back in business.

Second, keep tabs on when you renamed objects. If the goal is to truly get rid of them, having gobs of tables, views, sprocs, and other objects with a ‘zzz’ (or other) prefix doesn’t de-clutter the database. Instead it’s like telling everyone you’re taking out the trash when you’re just throwing it into a corner.  But if you keep tabs on WHEN you renamed objects and then set a calendar reminder (say for 30 days) later to come in and remove a list of object-names, you’ll be giving yourself a buffer/window where end-users and applications may ‘manifest’ with a need to use the object you’ve slated for termination – and you can simply rename it and move on.

Third, when you DO decide to finally DROP the object, you’re doing so for a good cause – to keep your database streamlined. But don’t kid yourself, even the most banal data or most trivial sproc can be a total pain to recreate if you find out 6 months later that the data or code you nuked is needed. Accordingly, set up a folder somewhere (or source code repository) that’s regularly backed up and each time you go to drop ANYTHING, script it first and drop it into a file with the object name and some comments about when and where it came from and so on.

Finding Potentially Non-Used Views

Finally, the thing that started this post was a need to find all NON-VALID sprocs, views, and udfs in an environment where I’m preparing a database for replication. Because when you initially snapshot a database and apply the snapshot to a replicated location, you’ll bump into all sorts of excitement (er, well, pain) when you’ve got older, crufty, views/sprocs/udfs that reference invalid objects – because when the snapshot agent goes to apply your initial snapshot, you’ll bump into syntax errors when trying to create/script the creation of code that references objects that don’t exist. (Funny how that works.)

Consequently, I’m working on a process to identify views, sprocs, and udfs (maybe even triggers) that aren’t valid. And, the way to find these is sadly… tough – because SQL Server has no problem letting invalid objects ‘languish’ after the objects they reference have been changed or modified. But, my thought is that if I can find a list of objects that aren’t regularly used, I can then evaluate them, and mark any for removal as needed.

And the process I take for this is:

Getting to the Point – Finding Potentially Non-Used Views

At any rate, with that approach defined and in place, I found that finding sprocs and udfs was pretty easy. (For a great overview of some options to do that, see Looking for Unused Stored Procedures in SQL Server 2008 by Glenn Berry (blog).)

But, when it came to finding views, I had to do a bit more work – and querying of the plan cache, resulting in the following query (that uses similar semantics to Glenn’s approach for finding procs):

USE GOSELECT v.name [ViewName]FROM sys.views vWHERE v.is_ms_shipped = 0EXCEPT SELECT o.Name [ViewName]FROM master.sys.dm_exec_cached_plans pCROSS APPLY sys.dm_exec_sql_text(p.plan_handle) tINNER JOIN sys.objects o ON t.objectid = o.object_idWHERE t.dbid = DB_ID()GO

And, again, those views aren’t necessarily non-used. They’re just views that aren’t in the plan cache – which makes it easier for me to know which views to focus my time on (rather than trying to script/ALTER them all).