Link to home
Start Free TrialLog in
Avatar of SanPrg
SanPrgFlag for United States of America

asked on

Query

Hi,

I need to know who updated the SProc and SQL Job last month.
Thanks
Avatar of Tony303
Tony303
Flag of New Zealand image

For the SProc question.


In SSMS, right click on DB (or Server), choose Reports / Standard Reports / Schema Changes History.

Hopefully the data goes back far enough to last month.

T
Hi SanPrg

Finding the list of Stored Procedures modified and created for last x days is also possible using sql query
-- Stored Procedures MODIFIED within 7 days 
SELECT name
 FROM sys.objects
 WHERE type = 'P'
 AND DATEDIFF(D,modify_date, GETDATE()) < 7

-- Stored Procedures CREATED within 7 days 
SELECT name
 FROM sys.objects
 WHERE type = 'P'
 AND DATEDIFF(D,create_date, GETDATE()) < 7

Open in new window


Raj
To get the list of Jobs modified in last 100 days, use the below query

select s.name,l.name, s.date_created, s.date_modified
 from  msdb..sysjobs s 
 left join master.sys.syslogins l on s.owner_sid = l.sid
 where DATEDIFF(D, s.date_modified, GETDATE()) < 100

Open in new window


Raj
Avatar of SanPrg

ASKER

Hi Raj,
So far it's good but I need to know who modified SProc and Jobs.
ASKER CERTIFIED SOLUTION
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SanPrg

ASKER

Thanks