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 2008 Forums
 Transact-SQL (2008)
 List of SP

Author  Topic 

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-11-21 : 17:48:18
Hi guys,

Need help, how i can get the list of SP in my Database that were not accessed in the last 3 Month?

Please advise.

Thank You.

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-11-21 : 19:32:14
I am trying to run below query

select
DB_NAME(database_id) as "database_name"
, OBJECT_NAME(object_id, database_id) as "procedure_name"
, last_execution_time
from
sys.dm_exec_procedure_stats
WHERE DB_NAME(database_id) = 'MYDB'
ORDER BY last_execution_time DESC

Question = Which time i am receving " last_execution_time" Most of the SP time saying todays date. Please guide me.

Thank You.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2012-11-21 : 22:32:56
Hi Sonu,

Run the following sql to find the last execution date.

SELECT p.*
FROM sys.procedures AS p
LEFT JOIN sys.dm_exec_procedure_stats AS s ON s.[object_id] = p.[object_id]
WHERE s.object_id IS NULL;

Thanks,
Sri.
Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-11-21 : 23:24:04
Hi Srimami, Thank you for your reply.

Modified Date is my last execution date?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-22 : 00:28:25
quote:
Originally posted by Sonu619

Hi Srimami, Thank you for your reply.

Modified Date is my last execution date?



Modified_Date is the date when you modified(i.e. ALTER) your procedure.

You can get last_execution_time from sys.dm_exec_procedure_stats

--
Chandu
Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-11-22 : 00:46:09
If i change my query to Below Query,
I am getting S.Last_Execution_Time is Null..

SELECT p.*, S.Last_Execution_Time
FROM sys.procedures AS p
LEFT JOIN sys.dm_exec_procedure_stats AS s ON s.[object_id] = p.[object_id]
WHERE s.object_id IS NULL;

and If i run Below Query

select
DB_NAME(database_id) as "database_name"
, OBJECT_NAME(object_id, database_id) as "procedure_name"
, last_execution_time
from
sys.dm_exec_procedure_stats
WHERE DB_NAME(database_id) = 'MYDB'
ORDER BY last_execution_time DESC

I am getting only Novembers data.Mean 80% of SP last_execution_time is November. I have more than 2000 SP and i am only seeing those who has last_execution_time in November.
Any advice????
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-22 : 01:22:39
[code]

-- Get list of all SPs in the current database (SQL 2005 and 2008)
SELECT p.name AS 'SP Name', p.create_date, p.modify_date
FROM sys.procedures AS p
WHERE p.is_ms_shipped = 0
ORDER BY p.name;


-- Get list of possibly unused SPs (SQL 2008 only)
SELECT p.name AS 'SP Name' -- Get list of all SPs in the current database
FROM sys.procedures AS p
WHERE p.is_ms_shipped = 0

EXCEPT

SELECT p.name AS 'SP Name' -- Get list of all SPs from the current database that are in the procedure cache
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.object_id = qs.object_id
WHERE p.is_ms_shipped = 0;
[/code]

You can get only Last Modified date for unused procedures....
For recently used procedures last_execution_date is available in cache procedure sys.dm_exec_procedure_stats

--
Chandu
Go to Top of Page
   

- Advertisement -