sql serversql-server-2012
I use sys.dm_db_index_usage_stats.last_user_seek, sys.dm_db_index_usage_stats.last_user_scan and sys.dm_db_index_usage_stats.last_user_lookup to query last time a table was read.
For views, functions and stored procedures I'm using sys.objects but it doesn't have any field for read datetime.
Is there any system table where I can see the last time these types of objects were read/used?
My objective is to find objects that aren't being used and possibly drop them.
You can look in the plan cache to get a pretty good idea of Stored Procedure usage. Take this query, for instance:
select
db_name(st.dbid) as database_name,
object_name(st.objectid) as name,
p.size_in_bytes / 1024 as size_in_kb,
p.usecounts,
st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id('SomeDatabase')
order by p.usecounts desc
This will give you the usecounts of the stored procedures that are cached pertaining to SomeDB.
Note: the plan cache contains the execution plans. This retention of these plans has many factors involved. Whereas this will give you a good idea of what is being used and how often, it's definitely not the running total of stored procedures and how often/when they were executed.
Sounds like you've already made the decision to use T-SQL for your back-end. For OWASP reasons, you are correct in that calling only stored procedures from your data access layer is the safest approach to avoid SQL injection.
Stored procedures and functions are similar in that you can pass arguments for the parameter list. They also can do similar things. Code re-use is a good thing and will save you in the end when fixing bugs or making enhancements in the future. So find the logic that is similar and re-factor that code to functions. If you want to return tables (single result set), use table based functions. If you want to return single values, use scalar functions. Note, you won't be able to perform permanent environment changes with functions (no CRUD operations are allowed... CREATE/INSERT, READ, UPDATE, DELETE). It's also good practice to wrap your stored procedures with error/exception handling and return those exceptions to the server, to let your logging handle them appropriately. Also note that it's common to return multiple result sets in a single call to a stored procedure (3 SELECT statements for example). In your application code, you'd handle each table individually in the response.
Here are some differences:
Best Answer
Unfortunately, there is no 100% accurate way to determine this. However, you can get as close as possible by examining all code that could potentially make DB calls, as well as the definitions of all of your DB objects and SQL Server Agent jobs. There are often code paths that are infrequently accessed and hence could easily not show up in DMVs, extended events, SQL Profiler traces, or even custom logging. Checking ALL code will get you as close as possible.
The only area left out of an exhaustive code search is ad hoc queries that are not in code anywhere. In this case DMVs, traces, etc at least have the potential to identify usage, but are certainly no guarantee.