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:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| InstanceName | SYSNAME | Name of current SQL Server instance. |
| DatabaseName | SYSNAME | Name of database. |
| Location | NVARCHAR(1024) | Operating-system file name. |
| FileSize | FLOAT | Current file size, in MB. |
| LastChangeInDB | DATETIME | Modification date maximal value from all tables within database. |