SQL Server - Last schema changes

Compatible from SQL Server 2008

Shows information about schema changes done in databases by DDL operations. This is quick listing for all databases containing information about last schema changes done in every database.

TIP: If you want to go deep and get more details about individual changes use SSMS built in report on database level Reports -> Standard Reports -> Schema Changes History.

-- create temp table for results
IF OBJECT_ID('tempdb..#DatabaseReport') IS NOT NULL DROP TABLE #DatabaseReport
GO
CREATE TABLE #DatabaseReport (
	InstanceName SYSNAME NULL,
	DatabaseName SYSNAME NULL, 
	Location NVARCHAR(1024) NULL,
	FileSize NVARCHAR(256) NULL,
	LastChangeInDB DATETIME NULL
);

-- go through all databases
EXEC sp_MSForEachDB 
'Use ?;
WITH fs AS
(
    SELECT database_id, type, size * 8.0 / 1024 size
    FROM sys.master_files
)
INSERT INTO #DatabaseReport (InstanceName, DatabaseName, Location, FileSize, LastChangeInDB) 
SELECT 
	@@SERVERNAME AS InstanceName,
	''?'' AS DatabaseName, 
	physical_name AS Location,
	(SELECT SUM(size) FROM fs WHERE type = 0 AND fs.database_id = db.database_id) AS FileSize ,	
	(SELECT MAX(modify_date) FROM sys.tables) AS LastChangeInDB  
FROM 
	sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id
WHERE mf.type = 0
	AND db.name = ''?'''

SELECT * FROM #DatabaseReport ORDER BY InstanceName, DatabaseName

DROP TABLE #DatabaseReport

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
InstanceName SYSNAMEName of current SQL Server instance.
DatabaseNameSYSNAMEName of database.
LocationNVARCHAR(1024)Operating-system file name.
FileSizeFLOATCurrent file size, in MB.
LastChangeInDBDATETIMEModification date maximal value from all tables within database.

Leave a Reply

Your email address will not be published. Required fields are marked *