20

I've added and modified several (new and existing resp.) tables and stored procs, for a particular database and server, in last 3 months.

I was thinking if there's any SQL query by which I can determine all those changes.

Thanks.

Kings
  • 1,551
  • 12
  • 32
  • 52

3 Answers3

67

Query the sys.objects table to find the objects that changed and filter by modify_date and type; U = User table, P = Stored procedure. 

select * 
from sys.objects 
where (type = 'U' or type = 'P') 
  and modify_date > dateadd(m, -3, getdate())

This approach will tell you what objects have changed, but not the specific changes.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tjhazel
  • 772
  • 7
  • 12
  • tjhazel, thnks. But results are too many and inlcudes other developer changes as well. Is there a way where I could filter the search based on the "logged_in user" who actually modified the mentioned change. – Kings May 16 '14 at 19:09
  • 2
    Needed this, thank you. If possibly, it would be lovely to include the related database name also. I've joined sys.schemas and gathered the schema.name for better info. – Beytan Kurt Dec 03 '15 at 12:33
13

Hi you can get the changed/modified db object details with this query

select name,create_date,modify_date
from sys.procedures
order by modify_date desc

Thanks

Tapan kumar
  • 6,719
  • 1
  • 24
  • 25
-7

I don't think you're going to be able to find what you're looking for. SQL Server just doesn't track that information out of the box.

To handle this in the future, you can use some kind of source control (redgate, for one: http://www.red-gate.com/products/sql-development/sql-source-control),

or you can set up a DDL trigger (one such technique is described here: https://dba.stackexchange.com/questions/33541/how-to-keep-history-of-sql-server-stored-procedure-revisions/33544#33544).

Community
  • 1
  • 1
Jerrad
  • 5,240
  • 1
  • 18
  • 23