These may be useful to determine when objects have been changed. We might infer from this info when was the last formal deployment of a database.
— get most recently changed object of each type
SELECT o.name
,SCHEMA_NAME(o.schema_id) AS ‘SchemaName’
,COALESCE(OBJECT_NAME(o.parent_object_id), ”) AS ‘ParentName’
,o.type
,o.type_desc
,o.create_date
,o.modify_date
FROM sys.objects o
JOIN(SELECT type
,’LateDate’ = MAX(modify_date)
FROM sys.objects
WHERE is_ms_shipped = 0
AND OBJECT_SCHEMA_NAME(object_id) <> ‘tSQLt’
GROUP BY type
) xo
ON o.type = xo.type
AND o.modify_date = xo.LateDate
ORDER BY o.modify_date DESC
,o.type
,o.name
— get 20 most recently changed objects
SELECT TOP 20
o.name
,SCHEMA_NAME(o.schema_id) AS ‘SchemaName’
,COALESCE(OBJECT_NAME(o.parent_object_id), ”) AS ‘ParentName’
,o.type
,o.type_desc
,o.create_date
,o.modify_date
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.parent_object_id = 0
–AND o.type = ‘U’
ORDER BY o.modify_date DESC
— get 20 most recently changed objects that are not tSQLt or tSQLt tests
SELECT TOP 20
SCHEMA_NAME(o.schema_id) AS ‘SchemaName’
,o.name
,COALESCE(OBJECT_NAME(o.parent_object_id), ”) AS ‘ParentName’
,o.type
,o.type_desc
,o.create_date
,o.modify_date
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.parent_object_id = 0
–AND o.type = ‘U’
AND OBJECT_SCHEMA_NAME(o.object_id) NOT LIKE ‘Z%’
AND OBJECT_SCHEMA_NAME(o.object_id) <> ‘tSQLt’
ORDER BY o.modify_date DESC
