Wednesday, January 18, 2012

List the modified objects in SQL Server

For viewing the modified date of Stored Procs and UDF alone:
Routine_name, Routine_Type, Created, Last_altered
From Information_schema.routines
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:
[name] as 'Object Name',
[type] as 'Object Type',
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

No comments:

Post a Comment