Latest Schema Snapshot for all databases

[chadbaldwin]

I've been needing a view that can show me at the very top level information about all of the latest schema snapshots so I just threw this custom report together.

I'm still figuring out what extra data I want to include in the output, but so far I'm liking what I have.

/*
	Latest Schema Snapshots
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2024-10-10 11:42:11 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER   PROCEDURE [UserReport].[usp_LatestSchemaSnapshots] (
	@InstanceIDs IDs READONLY
)
AS
BEGIN
	SELECT i.InstanceID
		, i.InstanceGroupName
		, ss.DatabaseID
		, DatabaseName = d.[name]
		, ss.SnapshotDate
		, ss.ValidatedDate
		, ValidForDays = DATEDIFF(d, ss.SnapshotDate, ss.ValidatedDate)
		, DaysSinceValidation = DATEDIFF(d, ss.ValidatedDate, GETUTCDATE())
		, ss.Created
		, ss.Modified
		, ss.Dropped
		, ContainsDBChange = CONVERT(bit, IIF(
			EXISTS (
				SELECT *
				FROM dbo.DBSnapshotDiff(ss.DatabaseID, ss.SnapshotDate) sd
				WHERE sd.ObjectType = 'DB'
					AND sd.[Action] = 'Modified'
			), 1, 0))
		, IsFirstSnapshot = CONVERT(bit, IIF(ss2.SnapshotCount = 1, 1, 0))
		, SnapshotCaptureDuration = sl.Duration
	FROM dbo.Instances i
		JOIN dbo.[Databases] d ON i.InstanceID = d.InstanceID
		CROSS APPLY ( -- latest snapshot per database
			SELECT TOP(1) ss.DatabaseID, ss.SnapshotDate, ss.ValidatedDate, ss.Created, ss.Modified, ss.Dropped
			FROM dbo.DDLSnapshots ss
			WHERE ss.DatabaseID = d.DatabaseID
			ORDER BY ss.SnapshotDate DESC
		) ss
		JOIN dbo.DDLSnapshotsLog sl ON sl.DatabaseID = d.DatabaseID AND sl.SnapshotDate = ss.SnapshotDate
		CROSS APPLY (SELECT SnapshotCount = COUNT(*) FROM dbo.DDLSnapshots ss WHERE ss.DatabaseID = d.DatabaseID) ss2
		CROSS APPLY (SELECT IsFirstSnapshot = CONVERT(bit, IIF(ss2.SnapshotCount = 1, 1, 0))) fs
	WHERE i.IsActive = 1 AND d.IsActive = 1
		AND EXISTS (SELECT * FROM @InstanceIDs ii WHERE ii.ID = i.InstanceID)
	ORDER BY d.[name];
END

GO
/* Error scripting pickers Value cannot be null. (Parameter 'source') */

/* Report customizations in GUI */
DELETE dbo.CustomReport
WHERE SchemaName = 'UserReport'
AND ProcedureName = 'usp_LatestSchemaSnapshots'

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','usp_LatestSchemaSnapshots','{
  "ReportName": "Latest Schema Snapshots",
  "TriggerCollectionTypes": [],
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {},
      "CellFormatString": {
        "Created": "N0",
        "Modified": "N0",
        "Dropped": "N0",
        "SnapshotDate": "yyyy-MM-dd HH:mm:ss.fff",
        "ValidatedDate": "yyyy-MM-dd HH:mm:ss.fff",
        "SnapshotCaptureDuration": "N0"
      },
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [
        {
          "Key": "InstanceID",
          "Value": {
            "Width": 84,
            "DisplayIndex": -1
          }
        },
        {
          "Key": "InstanceGroupName",
          "Value": {
            "Width": 141,
            "Visible": true,
            "DisplayIndex": 1
          }
        },
        {
          "Key": "DatabaseID",
          "Value": {
            "Width": 89,
            "DisplayIndex": 2
          }
        },
        {
          "Key": "DatabaseName",
          "Value": {
            "Width": 186,
            "Visible": true,
            "DisplayIndex": 3
          }
        },
        {
          "Key": "SnapshotDate",
          "Value": {
            "Width": 174,
            "Visible": true,
            "DisplayIndex": 4
          }
        },
        {
          "Key": "ValidatedDate",
          "Value": {
            "Width": 175,
            "Visible": true,
            "DisplayIndex": 5
          }
        },
        {
          "Key": "ValidForDays",
          "Value": {
            "Width": 94,
            "Visible": true,
            "DisplayIndex": 6
          }
        },
        {
          "Key": "DaysSinceValidation",
          "Value": {
            "Width": 129,
            "Visible": true,
            "DisplayIndex": 7
          }
        },
        {
          "Key": "Created",
          "Value": {
            "Width": 69,
            "Visible": true,
            "DisplayIndex": 8
          }
        },
        {
          "Key": "Modified",
          "Value": {
            "Width": 72,
            "Visible": true,
            "DisplayIndex": 9
          }
        },
        {
          "Key": "Dropped",
          "Value": {
            "Width": 73,
            "Visible": true,
            "DisplayIndex": 10
          }
        },
        {
          "Key": "ContainsDBChange",
          "Value": {
            "Width": 125,
            "Visible": true,
            "DisplayIndex": 11
          }
        },
        {
          "Key": "IsFirstSnapshot",
          "Value": {
            "Width": 104,
            "Visible": true,
            "DisplayIndex": 12
          }
        },
        {
          "Key": "SnapshotCaptureDuration",
          "Value": {
            "Width": 169,
            "Visible": true,
            "DisplayIndex": 13
          }
        }
      ],
      "ResultName": "Result0",
      "LinkColumns": {},
      "CellHighlightingRules": {
        "Created": {
          "Rules": [
            {
              "BackColor": "185, 255, 185",
              "BackColorDark": "0, 113, 0",
              "Font": "Consolas, 9pt",
              "Value1": "0",
              "ConditionType": "GreaterThan"
            },
            {
              "BackColor": "241, 241, 246",
              "BackColorDark": "37, 42, 46",
              "Font": "Consolas, 9pt",
              "ConditionType": "All"
            }
          ],
          "TargetColumn": "Created"
        },
        "Dropped": {
          "Rules": [
            {
              "BackColor": "255, 196, 196",
              "BackColorDark": "157, 0, 0",
              "Font": "Consolas, 9pt",
              "Value1": "0",
              "ConditionType": "GreaterThan"
            },
            {
              "BackColor": "241, 241, 246",
              "BackColorDark": "37, 42, 46",
              "Font": "Consolas, 9pt",
              "ConditionType": "All"
            }
          ],
          "TargetColumn": "Dropped"
        },
        "Modified": {
          "Rules": [
            {
              "BackColor": "255, 216, 176",
              "BackColorDark": "151, 75, 0",
              "Font": "Consolas, 9pt",
              "Value1": "0",
              "ConditionType": "GreaterThan"
            },
            {
              "BackColor": "241, 241, 246",
              "BackColorDark": "37, 42, 46",
              "Font": "Consolas, 9pt",
              "ConditionType": "All"
            }
          ],
          "TargetColumn": "Modified"
        },
        "SnapshotCaptureDuration": {
          "Rules": [
            {
              "BackColor": "241, 241, 246",
              "BackColorDark": "37, 42, 46",
              "Font": "Consolas, 9pt",
              "ConditionType": "All"
            }
          ],
          "TargetColumn": "SnapshotCaptureDuration"
        },
        "SnapshotDate": {
          "Rules": [
            {
              "BackColor": "241, 241, 246",
              "BackColorDark": "37, 42, 46",
              "Font": "Consolas, 9pt",
              "ConditionType": "All"
            }
          ],
          "TargetColumn": "SnapshotDate"
        },
        "ValidatedDate": {
          "Rules": [
            {
              "BackColor": "241, 241, 246",
              "BackColorDark": "37, 42, 46",
              "Font": "Consolas, 9pt",
              "ConditionType": "All"
            }
          ],
          "TargetColumn": "ValidatedDate"
        }
      }
    }
  }
}')

[DavidWiseman]

Thanks for sharing!