CODEWITHSUNDEEP
sql-server
Charlie RC
Charlie RC

Reputation: 219

MS-SQL history of stored procedures

We have about two dozen databases in production and each probably has a couple hundred stored procedures, views and tables. This data has been in daily use since around 2000 (starting with SQL v7). We have not been that diligent with keeping the database object usage documented and knowing which of these procedures, views and tables are still relevant, so my guess is that we have maybe 10% (or I maybe a lot more) of these objects that are no longer needed. Problem is, without going through 10s of thousands of lines of code, we can't be sure what is relevant and what is not. In the past, we have used the "modify name and see what breaks" method and that is still a one by one process and has the potential to cause some real problems.

Is there any way, in SQL2005, to determine when a procedure, view or table was last called, or the table data was appended or edited?

I think there may be a way to do individual object using the PM, but there are way to many objects to do then one by one. I need a way to get this info for All objects in a particular database.

Any ideas?

edit: added reference to SQL2005

Upvotes: 2

Views: 19863

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28890

I think you are looking for sys.dm_exec_procedure_stats dmv.

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name', 
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;

Source:https://msdn.microsoft.com/en-us/library/cc280701.aspx

if you are looking for table usage:below test data can help you understand more,observe scans,seeks,updates.

create table test2
(
id int,
name int
)

select * from test2

insert into test2
select 1,1

select * from sys.dm_db_index_usage_stats where object_id=object_id('test2')

select * from test2

insert into test2
select 1,1

select * from sys.dm_db_index_usage_stats where object_id=object_id('test2')

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29619

You can use sp_depends to see what depends on what. It shouldn't be too hard to write a script which runs this proc on each object in your database. In SQL Server 2014, you can query sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities to look for orphaned objects.

As @theGameIsWar writes, you can also find the execution data for stored procedures.

However, none of this is totally safe on old, long-running applications - sp_depends doesn't capture dynamic SQL in a stored procedure, and who knows if there's a direct "select query" which bypasses your stored procedures?

You may want to invest in a database versioning tool - I like the Redgate one. It won't fix the retrospective problem, but it will make it much easier to refactor, and it will keep the system cleaner in the future.

Upvotes: 1

Related Questions