SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 List of SP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts

Posted - 11/21/2012 :  17:48:18  Show Profile  Reply with Quote
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

195 Posts

Posted - 11/21/2012 :  19:32:14  Show Profile  Reply with Quote
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 - 11/21/2012 :  22:32:56  Show Profile  Reply with Quote
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

195 Posts

Posted - 11/21/2012 :  23:24:04  Show Profile  Reply with Quote
Hi Srimami, Thank you for your reply.

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

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 11/22/2012 :  00:28:25  Show Profile  Reply with Quote
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

195 Posts

Posted - 11/22/2012 :  00:46:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 11/22/2012 :  01:22:39  Show Profile  Reply with Quote


-- 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;


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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000