Automating DDL Change Detection and Reporting in MS SQL Server | Monitor and Report DDL Changes

Join this channel to get access to perks:
/ @mssqldbatechsupport
Join the Telegram Group for the scripts and assistance:
t.me/+sDwstoLc...
You Can Also Download the scripts from below folder
drive.google.c...
We strongly believe there is always chance of betterment, so suggestions are most welcome.
Happy learning, and All the Best in your professional journey!
The journey of improvement is ongoing and never be an end.
Connect With me,
LinkedIn Page --- / mssqldbatechteam
Facebook Page --- www.facebook.c...
Twitter --- / mssqldbasupport
Instagram --- / mssqldbatechsupport
Email --- [e-mail]
Telegram --- t.me/mssqldbaw...
#azuresql #azure #sqldba #sqlserverdba #sql #sqlserver #sqlserverdeveloper #performance #performancetuning #performanceoptimization #mssql #mssqlserver #mssqlserverdba
Thank you!
MS SQL DBA Tech Support
[e-mail]

[@MSSQLDBATechSupport]

Use AdventureWorks2019
Go
CREATE TABLE TableStructureSnapshot (
TableName VARCHAR(255),
ColumnName VARCHAR(255),
DataType VARCHAR(255),
CHARACTER_MAXIMUM_LENGTH VARCHAR(max),
IsNullable VARCHAR(3),
TableType VARCHAR(50),
Insertedate DATETIME DEFAULT GETDATE(),
PRIMARY KEY (TableName, ColumnName)
);
INSERT INTO TableStructureSnapshot (TableName, ColumnName, DataType,CHARACTER_MAXIMUM_LENGTH, IsNullable, TableType)
SELECT
c.table_name,
c.column_name,
c.data_type,
C.CHARACTER_MAXIMUM_LENGTH,
c.is_nullable,
t.table_type
FROM
information_schema.columns c
JOIN
information_schema.tables t
ON
c.table_name = t.table_name
--select * from TableStructureSnapshot where tablename = 'errorlog'
--drop table TableStructureSnapshot
--Truncate table TableStructureSnapshot
--Made Changes
Create table testing (id int)
Go
Create table Testing1 (id int)
Go
Alter table testing Add names varchar(10)
Go
Alter Table errorlog add rollno varchar(10)
Go
ALTER TABLE errorlog
ALTER COLUMN ErrorProcedure VARCHAR(100);
--new table created
SELECT
Currents.TableName
FROM
(SELECT table_name AS TableName
FROM information_schema.tables
WHERE table_type = 'BASE TABLE') AS Currents
LEFT JOIN
TableStructureSnapshot AS Snapshots
ON
Currents.TableName = Snapshots.TableName
WHERE
Snapshots.TableName IS NULL;
-- Old Table Deleted:
SELECT
Distinct(Snapshots.TableName)
FROM
TableStructureSnapshot AS Snapshots
LEFT JOIN
information_schema.tables AS Currents
ON
Snapshots.TableName = Currents.table_name
WHERE
Currents.table_name IS NULL;
-------------------------------------------
--New Coulumns Detected..
IF OBJECT_ID('tempdb..#ComparisonResults') IS NOT NULL
DROP TABLE #ComparisonResults;
-- Create the temporary table
CREATE TABLE #ComparisonResults (
TableName VARCHAR(255),
ColumnName VARCHAR(255),
DataType VARCHAR(255),
IsNullable VARCHAR(3)
);
-- Insert the comparison results into the temporary table
INSERT INTO #ComparisonResults (TableName, ColumnName, DataType, IsNullable)
SELECT
curr.TableName,
curr.ColumnName,
curr.DataType,
curr.IsNullable
FROM
(SELECT table_name AS TableName, column_name AS ColumnName, data_type AS DataType, is_nullable AS IsNullable
FROM information_schema.columns) AS curr
FULL OUTER JOIN
TableStructureSnapshot AS snap
ON
curr.TableName = snap.TableName
AND curr.ColumnName = snap.ColumnName
WHERE
curr.TableName IS NULL
OR snap.TableName IS NULL
OR curr.ColumnName IS NULL
OR snap.ColumnName IS NULL
OR curr.DataType snap.DataType
OR curr.IsNullable snap.IsNullable;
-- Select from the temporary table to view the results
SELECT * FROM #ComparisonResults where TableName is not null
-- Optionally, drop the temporary table when done
DROP TABLE #ComparisonResults;
-- Columns Deleted Deteted.
SELECT
TableName,
ColumnName,
'Columns deleted or Renamed' AS Status
FROM
TableStructureSnapshot tss
WHERE
NOT EXISTS (
SELECT *
FROM information_schema.columns ic
WHERE tss.TableName = ic.TABLE_NAME
AND tss.ColumnName = ic.COLUMN_NAME
)
AND EXISTS (
SELECT *
FROM information_schema.tables it
WHERE it.TABLE_NAME = tss.TableName
);
----New Coulmns added
SELECT
ic.TABLE_NAME AS TableName,
ic.COLUMN_NAME AS ColumnName,
'New Columns Added' AS Status
FROM
information_schema.columns ic
WHERE
EXISTS (
SELECT 1
FROM TableStructureSnapshot tss
WHERE tss.TableName = ic.TABLE_NAME
)
AND NOT EXISTS (
SELECT 1
FROM TableStructureSnapshot tss
WHERE tss.TableName = ic.TABLE_NAME
AND tss.ColumnName = ic.COLUMN_NAME
);
---Detect datatype change
SELECT
tss.TableName,
tss.ColumnName,
tss.DataType AS OldDataType,
ic.DATA_TYPE AS NewDataType
FROM
TableStructureSnapshot tss
JOIN
information_schema.columns ic
ON
tss.TableName = ic.TABLE_NAME
AND tss.ColumnName = ic.COLUMN_NAME
JOIN
information_schema.tables it
ON
it.TABLE_NAME = tss.TableName
WHERE
tss.DataType ic.DATA_TYPE
AND it.TABLE_TYPE = 'BASE TABLE';
---------------
---Detect datatype size change
SELECT
tss.TableName,
tss.ColumnName,
tss.CHARACTER_MAXIMUM_LENGTH AS OldMaxLength,
ic.CHARACTER_MAXIMUM_LENGTH AS NewMaxLength
FROM
TableStructureSnapshot tss
JOIN
information_schema.columns ic
ON
tss.TableName = ic.TABLE_NAME
AND tss.ColumnName = ic.COLUMN_NAME
JOIN
information_schema.tables it
ON
it.TABLE_NAME = tss.TableName
WHERE
tss.CHARACTER_MAXIMUM_LENGTH ic.CHARACTER_MAXIMUM_LENGTH
AND it.TABLE_TYPE = 'BASE TABLE';

[@MSSQLDBATechSupport]

DECLARE @HTMLTablecount NVARCHAR(MAX);
DECLARE @HTMLtable NVARCHAR(MAX);
DECLARE @HTMLDeletedTables NVARCHAR(MAX);
DECLARE @HTMLNewColumns NVARCHAR(MAX);
DECLARE @HTMLDataTypeChange NVARCHAR(MAX);
DECLARE @HTMLDataTypeSizeChange NVARCHAR(MAX);
DECLARE @EmailBody NVARCHAR(MAX);
-- HTML for New Tables Created
SET @HTMLtable =
N'New Tables Created Report' +
N'' +
N'
New Tables Created
' +
CAST((
SELECT
Currents.TableName AS 'td'
FROM
(SELECT table_name AS TableName
FROM information_schema.tables
WHERE table_type = 'BASE TABLE') AS Currents
LEFT JOIN
TableStructureSnapshot AS Snapshots
ON
Currents.TableName = Snapshots.TableName
WHERE
Snapshots.TableName IS NULL
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'';
-- HTML for New Tables Count
SET @HTMLTablecount =
N'Total Count of Tables'+
N'' +
N'
New Tables Count
' +
CAST((
SELECT
Count(*) AS 'td'
FROM
information_schema.tables
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'';
-- HTML for Missing Tables
SET @HTMLDeletedTables =
N'Missing Tables Report' +
N'' +
N'
Missing Tables
' +
CAST((
SELECT
DISTINCT
Snapshots.TableName , ''
FROM
TableStructureSnapshot AS Snapshots
LEFT JOIN
information_schema.tables AS Currents
ON
Snapshots.TableName = Currents.table_name
WHERE
Currents.table_name IS NULL
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'';
-- HTML for New Columns Detected
-- Create a temporary table for column comparison results
IF OBJECT_ID('tempdb..#ComparisonResults') IS NOT NULL
DROP TABLE #ComparisonResults;
CREATE TABLE #ComparisonResults (
TableName VARCHAR(255),
ColumnName VARCHAR(255),
DataType VARCHAR(255),
IsNullable VARCHAR(3)
);
-- Insert comparison results into the temporary table
INSERT INTO #ComparisonResults (TableName, ColumnName, DataType, IsNullable)
SELECT
curr.TableName,
curr.ColumnName,
curr.DataType,
curr.IsNullable
FROM
(SELECT table_name AS TableName, column_name AS ColumnName, data_type AS DataType, is_nullable AS IsNullable
FROM information_schema.columns) AS curr
FULL OUTER JOIN
TableStructureSnapshot AS snap
ON
curr.TableName = snap.TableName
AND curr.ColumnName = snap.ColumnName
WHERE
curr.TableName IS NULL
OR snap.TableName IS NULL
OR curr.ColumnName IS NULL
OR snap.ColumnName IS NULL
OR curr.DataType snap.DataType
OR curr.IsNullable snap.IsNullable;
-- Generate HTML for New Columns Detected
SET @HTMLNewColumns =
N'New Columns Detected' +
N'' +
N'
TableName
ColumnName
DataType
IsNullable
' +
CAST((
SELECT
TD = TableName, '',
TD = ColumnName, '' ,
TD = DataType, '',
TD = IsNullable , ''
FROM
#ComparisonResults
WHERE
TableName IS NOT NULL
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'';
-- HTML for Datatype Changes
SET @HTMLDataTypeChange =
N'Datatype Changes Detected' +
N'' +
N'
TableName
ColumnName
OldDataType
NewDataType
' +
CAST((
SELECT
TD = tss.TableName, '',
TD = tss.ColumnName, '' ,
TD = tss.OldDataType, '' ,
TD = tss.NewDataType, ''
FROM
(SELECT
tss.TableName,
tss.ColumnName,
tss.DataType AS OldDataType,
ic.DATA_TYPE AS NewDataType
FROM
TableStructureSnapshot tss
JOIN
information_schema.columns ic
ON
tss.TableName = ic.TABLE_NAME
AND tss.ColumnName = ic.COLUMN_NAME
JOIN
information_schema.tables it
ON
it.TABLE_NAME = tss.TableName
WHERE
tss.DataType ic.DATA_TYPE
AND it.TABLE_TYPE = 'BASE TABLE'
) AS tss
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'';
-- HTML for Datatype Size Changes
SET @HTMLDataTypeSizeChange =
N'Datatype Size Changes Detected' +
N'' +
N'
TableName
ColumnName
OldMaxLength
NewMaxLength
' +
CAST((
SELECT
TD = tss.TableName, '',
TD = tss.ColumnName, '' ,
TD = tss.OldMaxLength, '' ,
TD = tss.NewMaxLength, ''
FROM
(SELECT
tss.TableName,
tss.ColumnName,
tss.CHARACTER_MAXIMUM_LENGTH AS OldMaxLength,
ic.CHARACTER_MAXIMUM_LENGTH AS NewMaxLength
FROM
TableStructureSnapshot tss
JOIN
information_schema.columns ic
ON
tss.TableName = ic.TABLE_NAME
AND tss.ColumnName = ic.COLUMN_NAME
JOIN
information_schema.tables it
ON
it.TABLE_NAME = tss.TableName
WHERE
tss.CHARACTER_MAXIMUM_LENGTH ic.CHARACTER_MAXIMUM_LENGTH
AND it.TABLE_TYPE = 'BASE TABLE'
) AS tss
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'';
-- Combine all sections into a single email body
SET @EmailBody = @HTMLTablecount+ N'' + @HTMLtable + N'' + @HTMLDeletedTables + N'' + @HTMLNewColumns + N'' + @HTMLDataTypeChange + N'' + @HTMLDataTypeSizeChange;
-- Send email with the combined HTML body
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'outlook',
@recipients = '[e-mail]',
@body = @EmailBody,
@body_format = 'HTML',
@subject = 'Database Health Report';
-- Optionally, drop the temporary table when done
IF OBJECT_ID('tempdb..#ComparisonResults') IS NOT NULL
DROP TABLE #ComparisonResults;

[@GuardianGaming-b3v]

Nice explanation...it would be great if you could add the user details as well to know who changed

[@sqlamit]

I was about to ask the same 😅

[@MSSQLDBATechSupport]

Thanks... we can achieve this in many ways.
1.using triggers
2.Using Schema changes report --->Instance-->Reports-->Standard Reports-->Schema changes report.
3.Using Performance condition Alerts.
we don't know when and who will change the Table structure. To monitor this we can create a trigger and it capture the change and load the change into the table.
using Schema changes report... we can use T SQL queries to see the same
the data as queries instead of GUI. why because every time we don't see the report using GUI. and it will not hold the old records if new changes are came... so we need to do is we need to create a table and periodically load these changes into the table.
Its all depends about your environment and your requirement which method you want to opt.

[@MSSQLDBATechSupport]

Replied

[@GuardianGaming-b3v]

@@MSSQLDBATechSupport thank you for explaining

[@MSSQLDBATechSupport]

Welcome

[@muzeebkhan471]

Thanks for sharing 🤝
Nice explanation 👍

[@MSSQLDBATechSupport]

Thanks for liking👍

[@vijayangovindaraj5124]

Thanks a lot.

[@MSSQLDBATechSupport]

Glad to help

[@richardtorrenueva5512]

Where to find that script in your shared google drive ?

[@MSSQLDBATechSupport]

Hi Richard
just now i created a folder with the name Database level changes tracking
in google drive link.
you can also get it from pinned comment