SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If stored procedure was created but never modified afterwards modified date and create date for that stored procedure are same.

SELECT
name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
----Change 7 to any other day value

Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7
----Change 7 to any other day value.

Date condition in above script can be adjusted to retrieve required data.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

78 thoughts on “SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

  1. There’s an error in your queries.

    The line:
    AND DATEDIFF(D,modify_date, GETDATE()) > 7

    should be:
    AND DATEDIFF(D,modify_date, GETDATE()) < 7

    Same with the create_date query. Your query checks modifications/creations MORE than 7 days old.

    Like

  2. Beyond finding out about procs changed, when; how do I tell the last date, value and who modified a database setting such as “Read_Committed_Snapshot”?

    I know I can test to see the current value with “is_read_committed_snapshot_on” from sys.databases but I need to know when the setting was changed and what value/whom set this type of property?

    How much info can I get? how?

    What events in SQL such as Update Statistics could turn this setting off without user intervention or user knowledge? How, separately perhaps, would I also audit such events/changes?

    Thanks

    Like

  3. Pingback: SQL SERVER - 2005 - Find Stored Procedure Create Date and Modified Date Journey to SQL Authority with Pinal Dave

  4. Thank you for the query…

    I need to add serial number to the record extracted from SQL server 2000. Is there any way to have my own set of serial number generated along with the query.
    ex:
    1 suresh 23
    2 samuel 39
    or

    Please suggest….

    Like

  5. Thank you for the query…

    I need to add serial number to the record extracted from SQL server 2000. Is there any way to have my own set of serial number generated along with the query.
    ex:
    1 suresh 23
    2 samuel 39
    or

    Please suggest….

    Like

  6. Hi Suresh
    Please try this query

    Select (ROW_NUMBER() OVER ( Order By Height)) as MyRowNumber , Height, CorrectedHeight from Results

    // Gandhi
    Skalar –
    Agaram Instruments
    Chennai

    Like

  7. is there a query to know the person who has made the change.. ie, if there has been a change in the sp / view / table, how to find who has done that change.

    Like

  8. Thank you so much, viewing the records for the last 7 days was the last task to complete my online application and I just want to say thanks for making it so easy.

    Like

    • select datename(month,dates) as month_name from
      (
      select dateadd(month,number,0) as dates from master..spt_values where type=’p’ and number between 0 and 11
      ) as t

      Like

  9. do this for sql server 2000 for same result :

    select *
    from information_schema.routines where Routine_Type=’procedure’
    AND DATEDIFF(D,last_altered, GETDATE()) <7
    select @@databasename

    if you want to find modified sp in the specific database
    add the below conditionL

    and specific_Catalog=’yourrdatabasename’

    Like

  10. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  11. Thanks for the query, but as an auditor I need to know who made the modification, in other words,
    I need know the:
    what was modified (object name),
    when (when that object was modified)
    and the
    who (who made the changes).

    Is this possible?

    Regards

    Julio Valencia

    Like

  12. Hi,

    I have run the below procedure in sql 2000 but i am getting the same date in both created and last_alterd column.But user is saying he has modified the Procedure 2 days back.

    select *
    from information_schema.routines where Routine_Type=’procedure’
    AND DATEDIFF(D,last_altered, GETDATE()) <7
    select @@databasename

    if you want to find modified sp in the specific database
    add the below conditionL

    and specific_Catalog=’yourrdatabasename’

    Kindly help for sql 2000.

    Like

  13. Hi,
    this is bangaram, i am new to sql server i have some doubts
    1.i have created 1 table and i have to update 1 attribute in that table how.
    2.in 1 table first attribute is sl.no it has to be generated number automatically , How can i do this

    Please reply fast

    Like

  14. Pinal,

    Code work, your scripts have always helped much.

    The following to codes are for SQL SVR 2000 version:

    Select *
    From sysobjects
    Where type =’P’
    and DateDiff(D,refdate,getdate()) < 7;

    Select *
    From sysobjects
    Where type ='P'
    and DateDiff(D,crdate,getdate()) < 7;

    Rajan this answers your question.

    Thanks

    Like

  15. This is just what I was looking for. I want to use it to update on the webserver only the modified stored procedures.

    Unfortunately stored procedures that I have just modified with “ALTER PROCEDURE” are still shown as modified many months ago.

    By what action is the modified date of a stored procedure updated?

    Thanks!

    Like

    • This is how I’ve done this on one of our projects and it works like a charm.

      SELECT o.modify_date, o.name, m.definition
      FROM sys.sql_modules m
      JOIN sys.objects o ON o.object_id = m.object_id
      WHERE o.modify_date > DATEADD(week, -1, GETDATE())

      If you want to filter out only stored procs change sys.objects to sys.procedures.

      Sidenote: there’s a funny thing which caused me a lot of head ache. The field sys.objects.name and the create clause in sys.sql_modules.definition can differ! This happens if you use sp_rename proc.

      Sidenote2: If you run ALTER PROC without any actual modifications to the proc, sys.objects.modify_date gets modified still. That is why I check sys.sql_modules.definition field also to prevent false positives.

      I hope this helps.

      Like

  16. Thank you! It is a great post. I’d like to clarify one thing. Both queries can return the same values. This can happen if I created a new stored procedure within the last 7 days and then updated it.

    Like

  17. Hi Pinal

    I need to generate the list of all tables whose structures were modified in the last 15 days. All tables whose data were modified should not be included in the list.

    Like

  18. Hi.. Thanks for all the info.. I have a query in my environment.. please let me know hw to achieve the below agenda :

    -> Monitor the environment and find if a new sql agent  job has been created. If yes report the details of the job; We can catch the new jobs and update the job with notifications assigned to person/DL responsible for the job.

    -> Monitors the environment and find status of Server level priviliges of all the logins and sends a report. We can  identify any changes in the priviliges of logins by comparing with the previous report

    Please help me.. Its urgent..

    Like

  19. Hi Pinal,

    Is there a way to find out who (which domain user) has created a login on the server? And who (which domain user) has modified the same login ? if they have modified, what have they edited, old value and new values to be specific ?

    select * from master.sys.syslogins — shows the createdate, updatedate and accdate but not the user info.

    sys.fn_tracegettable also has a limitation of only 5 traces.

    Can you help me ?

    Nitin.

    Like

  20. I need to build an sql of all users who have logged the last three months. I’ve trying to understand system sps for example whoisactive, but I dont undertand them. Any idea please.

    Like

  21. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s