A procedure behaves differently, but nobody remembers changing it. Its modify_date provides a starting clue, while the current definition and captured events supply the evidence that a timestamp cannot.
List Recent Objects by modify_date
The catalog stores creation and modification timestamps for schema-scoped objects. Filter those timestamps to locate candidates around the reported change window. Include schema and object type so similarly named objects do not get confused. Read the current database name in the output before following a result.
I begin with a bounded interval instead of sorting the entire catalog and guessing. Use the incident's approximate time and allow for uncertainty. Object timestamps use the server's local time convention. Keep that separate from UTC timestamps collected by an audit or external monitoring process.
DECLARE @Since datetime = DATEADD(DAY, -7, GETDATE());
SELECT DB_NAME() AS DatabaseName, s.name AS SchemaName,
o.name AS ObjectName, o.type_desc,
o.create_date, o.modify_date
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0 AND o.modify_date >= @Since
ORDER BY o.modify_date DESC, s.name, o.name;This is a read-only investigation. It changes no object and installs no capture mechanism. Catalog visibility depends on the account's permissions. Run through an approved identity with enough metadata access to answer the question. A partial catalog report should be labeled as partial evidence.
Interpret What Moves modify_date
ALTER statements update an object's modification metadata. For a table or view, index creation or alteration can also move the timestamp. That does not mean the table's business columns changed. Inspect the object type, index definitions, and accepted release records before attributing the date to one particular action.
Ordinary row inserts and updates do not make this a last-data-change timestamp. A busy table can retain an old schema date. Likewise, CREATE OR ALTER can advance a module's date even when a deployment reapplies the same effective definition. Metadata movement does not prove a behavior change.
Security changes require care too. Ownership alterations and related security DDL need review, while permission grants and denies do not provide a reliable comprehensive history through this field. Do not infer that an old timestamp proves unchanged access. Inspect permissions and captured security events when that is the real question.
Search modify_date Across Accessible Databases
Each database has its own catalog. The following script builds read-only SELECT statements across accessible online databases. QUOTENAME protects database identifiers, and escaped string literals protect displayed names. A fixed output collation prevents differing database collations from breaking the UNION ALL operation.
DECLARE @Since datetime = DATEADD(DAY, -7, GETDATE());
DECLARE @Sql nvarchar(max) = N'';
DECLARE @DatabaseName sysname;
DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE state = 0 AND HAS_DBACCESS(name) = 1;
OPEN DatabaseList;
FETCH NEXT FROM DatabaseList INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = @Sql + CASE WHEN @Sql = N'' THEN N'' ELSE N' UNION ALL ' END
+ N'SELECT N''' + REPLACE(@DatabaseName, N'''', N'''''')
+ N''' COLLATE Latin1_General_100_CI_AS AS DatabaseName,
s.name COLLATE Latin1_General_100_CI_AS AS SchemaName,
o.name COLLATE Latin1_General_100_CI_AS AS ObjectName,
o.type_desc COLLATE Latin1_General_100_CI_AS AS ObjectType,
o.create_date, o.modify_date
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS o
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0 AND o.modify_date >= @Since';
FETCH NEXT FROM DatabaseList INTO @DatabaseName;
END;
CLOSE DatabaseList;
DEALLOCATE DatabaseList;
IF @Sql <> N''
BEGIN
SET @Sql += N' ORDER BY modify_date DESC, DatabaseName, SchemaName, ObjectName;';
EXEC sys.sp_executesql @Sql, N'@Since datetime', @Since = @Since;
END;A database can become unavailable after the initial inventory. Record that failure and rerun its query separately when access returns. The script does not bypass permissions or force an unavailable database online. Check the included database population before describing the result as an instance-wide inventory.
Read the Current Module Definition
Once a procedure becomes a candidate, inspect its definition without changing it. sys.sql_modules provides stored text for SQL modules where it is visible. An encrypted definition or insufficient permission can produce NULL. That absence needs interpretation rather than a conclusion that the module contains no logic.
SELECT s.name AS SchemaName, o.name AS ObjectName,
o.modify_date, m.definition
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
LEFT JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.object_id = OBJECT_ID(N'dbo.ProcedureToReview');Replace the name with the identified procedure. Compare its accepted release definition from your controlled archive. A hash can help signal text differences, but a reviewed comparison explains them. Whitespace changes, altered SET context, and substantive predicate changes deserve different responses in the investigation.
I preserve the retrieved definition before asking for an explanation. That captures the actual state under review. An immediate redeployment can remove useful evidence about what the application used. Coordinate any later repair separately after the discrepancy and its operational effect are understood.
Separate Ownership From Authorship
Object ownership identifies a current security relationship. It does not identify the person who last changed the definition. Neither the schema owner nor the principal_id answers that historical question. A shared deployment login introduces another distinction between the executing account and the individual requesting the release.
The catalog retains the latest modification timestamp, not a sequence of all changes. Several edits between observations collapse into one latest value. Dropping and recreating an object changes its identity and creation history. A restored database also brings catalog evidence from its backup state rather than manufacturing a new audit trail.
Which evidence can connect this change to an authorized release? Look for a recorded deployment package, execution identity, approval, and captured DDL event. Match the object and time window across those sources. Avoid blaming the account currently owning the object because its name happens to be available.
Inspect Permissions as Current State
When the concern involves access changes, inspect the current permission rows. This query shows explicit object permissions for the selected object. It does not expand all inherited role permissions or show who issued a historical grant. The output describes the present catalog state only.
SELECT p.state_desc, p.permission_name,
u.name AS GranteeName
FROM sys.database_permissions AS p
JOIN sys.database_principals AS u
ON u.principal_id = p.grantee_principal_id
WHERE p.class = 1
AND p.major_id = OBJECT_ID(N'dbo.ProcedureToReview');Compare that state with an accepted earlier snapshot when available. Include roles, ownership, and explicit denies in an effective-access review. A single grant listing cannot calculate every route an identity has into the database. Keep the permission question distinct from the module-definition question.
Plan Lightweight Capture for Future Questions
A periodic read-only catalog export records observed definitions and dates. Save the capture time, server, database identity, and collection failures. Comparing those exports identifies changes between observations, but it cannot identify every intermediate action or its author. Choose a cadence that fits the required detection window.
SQL Server Audit or Extended Events can capture relevant DDL activity going forward. Define the event scope, retention, access controls, and collection health before an administrator configures it. Include permission events when security changes matter. This article's queries remain read-only and do not create that capture.
Use modify_date to narrow today's investigation, then retain stronger evidence for tomorrow. A timestamp is a helpful clue when its scope is understood. Pair modify_date with accepted definitions and a maintained event trail so the next unexplained change has more than a date attached.
Related reading on this blog: Reading the Default Trace and Keeping a Change Log for Every Database.
An object timestamp is not an audit trail, it is the latest catalog clue to a change.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
