Find recently modified objects

Observed sign or problem

A behavior changed after a deployment or we need to recognize which objects have received recent modifications. The catalog offers a useful date to narrow down the initial search.

What we need to distinguish

A schema operation changes the structural definition of the database. A deployment is the controlled process that applies one or more changes to an environment.

The recorded date indicates when SQL Server observed the object last modified by certain schema operations. It does not identify the author, the changed content, or the deployment that produced it.

Query

DECLARE @Dias INT = 30;
DECLARE @Desde DATETIME2 = DATEADD(DAY, -@Dias, SYSDATETIME());

SELECT
    s.name AS Esquema,
    o.name AS Objeto,
    o.type_desc AS Tipo,
    o.create_date AS FechaCreacion,
    o.modify_date AS FechaModificacion
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
    ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
  AND o.modify_date >= @Desde
  AND o.type IN ('U', 'V', 'P', 'FN', 'IF', 'TF', 'TR')
ORDER BY
    o.modify_date DESC,
    s.name,
    o.name;

Parameters or permissions

Change @Dias according to the period investigated. SYSDATETIME() uses the server’s date and time; Compare it to your environment’s time zone and deployment log.

The query includes visible tables, views, procedures, functions, and DML triggers. DDL triggers do not appear in sys.objects; for them use sys.triggers, as in search for triggers and their associated objects.

How to read the result

Causality means that one change produced the observed effect. Temporal proximity only shows correlation and does not prove it.

We sort from the most recent change to form a list of candidates. If the problem started yesterday, objects modified shortly before deserve review, but temporal proximity does not prove causality.

In tables and views, modify_date can also change when an index is created or altered. A recent row could represent index maintenance and not a column or code change.

A deployment process that deletes and recreates an object can alter both FechaCreacion and FechaModificacion. That is why it is advisable to compare both dates and consult the history of the repository or the deployment tool.

What this does establish

Allows you to identify visible objects whose recorded modification date falls within the chosen period. It is a quick way to narrow down an investigation.

What this still does not establish

A DDL audit records structural-change events; the current catalog does not replace that history or version control.

It does not indicate who made the change, what statement they executed, or what the previous definition was. It also does not log data changes or replace a DDL audit, version control, or deployment history.

A missing object may have been deleted; would no longer exist in this view to tell it.

Next reasonable check

Compare the candidates with the commit, script or deployed package. Review its definition and dependencies, and correlate the time with application logs, jobs, and server events before attributing the problem.

Search SQL Server