Monday, November 19, 2007

List the modified objects in SQL Server 2005

For viewing the modified date of Stored Procs and UDF alone:

Select
Routine_name, Routine_Type, Created, Last_altered
From Information_schema.routines
Where
Routine_type in ('PROCEDURE', 'FUNCTION')
Order by
Last_altered desc, Routine_type, Routine_name


For viewing the modified date of Stored Procs, UDF and Views:

We can query 'Sys.Objects' table and find out the list of Stored procs, UDFs, Views etc., which have got modified.

Code snippet:
Select
[name] as 'Object Name',
[type] as 'Object Type',
create_date,
modify_date
From sys.objects
Where [type] in ('P', 'FN', 'TF', 'V')
Order by Modify_Date desc, [type], [name]


The above query will list all 'SPs', 'UDFs' and 'Views' in the current database with its 'Created date' and 'Modified date'. We can further finetune this code to match our exact need!

For triggers
Check out create_date and modify_date columns in sys.triggers.
select * from sys.triggers

3 comments:

Anonymous said...

I found this is very useful vadi..
thanks a lot for your blog.
venki

Vadivel said...

Thanks Venki.

Anonymous said...

Thanks much for your help. I will never understand how I can type in a string in Google and out of 94,000 hits, the first one is what I want and it works perfectly. I appreciate the time that you took to put this tip online. It will save me a lot of problems in the future.