SanPrg
asked on
Query
Hi,
I need to know who updated the SProc and SQL Job last month.
Thanks
I need to know who updated the SProc and SQL Job last month.
Thanks
Hi SanPrg
Finding the list of Stored Procedures modified and created for last x days is also possible using sql query
Raj
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
Raj
To get the list of Jobs modified in last 100 days, use the below query
Raj
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
Raj
ASKER
Hi Raj,
So far it's good but I need to know who modified SProc and Jobs.
So far it's good but I need to know who modified SProc and Jobs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
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