Cleaning up unused SPs, Views and Functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Cleaning up unused SPs, Views and Functions

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2010-08-13 : 09:39:07

I'm getting rid of all old Stored Procs, Views etc and am runnign a query against each database such as:

SELECT o.name,o.xtype,o.crdate,o.refdate
FROM sys.syscomments c
INNER JOIN sys.sysObjects o ON o.ID=c.ID
WHERE c.text LIKE '%Pervasive%'

where Pervasive is an old system we don't use anymore.

My question is, is there a field in a table amongst these system tables that tells me when the SP, View etc was last accessed?

I thought it might have been refdate from the sysobjects table, but that doesn't update when I run a select against one of the views.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Kristen
Test

22859 Posts

Posted - 2010-08-13 : 09:53:23

sys.depends might tell you if anything else is dependant on that object. But not if anything is accessing it directly.

Remove it and wait for people to scream is about all you can do for VIEWs and TABLEs

For Stored Procedures you could modify them to LOG that they were called ...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-13 : 10:42:34

[code]
select query_plan
from sys.dm_exec_cached_plans cp
cross apply
sys.dm_exec_query_plan(cp.plan_handle) xp
where convert(varchar(max), query_plan) like '%YourProc%'
and objtype = 'Proc'[/code]

Note that if zero records are returned, it doesn't guarentee that the proc hasn't been called, just that it hasn't since the last service restart.

Once I'm pretty darn sure that an object is no longer in use, I'll rename it for 30 days or so before dropping.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-18 : 06:44:37

if the views are indexed...you can try querying
sys.dm_db_index_usage_stats dynamic management view

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources