There are times when our developers make changes to the database code but fail to update the source control accordingly! In those situation at the end of the day when we want to know the list of SQL objects which got modified on that day what would we do? Read on...
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.
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
|
No responses found. Be the first to respond and make money from revenue sharing program.
|