SP (Stored Procedure) Audit script for SQL Server 2005

I needed a script that showed the last change in time for the stored procedures in my database so I came up with the following. Certain of you may find it useful. Please comment if you have ideas for improvements.

–sp audit by Terry Grignon; for SQL Server 2005 and possibly newer versions
SELECT COUNT(DISTINCT modify_date) as [SP’s changed],
datepart(yyyy,modify_date) as ModificationYear,
datepart(dy,modify_date) as ModificationDayOfYear,
min(modify_date) as [First Change],
max(modify_date) as [Last Change]
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = ‘P’
group by datepart(yyyy,modify_date), datepart(dy,modify_date)
order by datepart(yyyy,modify_date), datepart(dy,modify_date);

SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear,
modify_date, datepart(dy,modify_date) as ModificationDayOfYear
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = ‘P’
order by datepart(yyyy,modify_date) asc,
datepart(dy,modify_date) asc, name;

About these ads

2 thoughts on “SP (Stored Procedure) Audit script for SQL Server 2005

  1. Not sure what you’re asking, Sara. You just need to copy the code into SQL Server Management Studio. You may need to clean up some syntax because of WordPress. The quotes around the P string should be single quotes (the key on the keyboard beside the Enter or Return key) but then you just run it. You will need sufficient privileges to run it.

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