Sql-Server
比較兩個不同表中的行，如果有差異，請發送郵件顯示差異
我想更改我的查詢，使其僅顯示已更改的值。我正在比較目前表和歷史表之間的值。查詢原樣顯示所有值，我必須跟踪郵件才能找到差異。我如何編寫查詢以僅顯示具有更改值的行？
DECLARE @Date Datetime = (SELECT TOP 1 CollectDate FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST ORDER BY CollectDate DESC) /*select * FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current NEW INNER JOIN SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST OLD ON OLD.ServerName = NEW.ServerName WHERE OLD.CollectDate = @Date */ --Changed or new IF EXISTS(SELECT * FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current WHERE ISNULL (ServerName, '_') +ISNULL (name, '_') +ISNULL (Type_Desc, '_') +ISNULL ([Public], '_') +ISNULL (SysAdmin, '_') +ISNULL (SecurityAdmin, '_') +ISNULL (ServerAdmin, '_') +ISNULL (SetupAdmin, '_') +ISNULL (ProcessAdmin, '_') +ISNULL (DiskAdmin, '_') +ISNULL (DBCreator, '_') +ISNULL (BulkAdmin, '_') +ISNULL (ControlServer, '_') NOT IN (SELECT ISNULL (ServerName, '_') +ISNULL (name, '_') +ISNULL (Type_Desc, '_') +ISNULL ([Public], '_') +ISNULL (SysAdmin, '_') +ISNULL (SecurityAdmin, '_') +ISNULL (ServerAdmin, '_') +ISNULL (SetupAdmin, '_') +ISNULL (ProcessAdmin, '_') +ISNULL (DiskAdmin, '_') +ISNULL (DBCreator, '_') +ISNULL (BulkAdmin, '_') +ISNULL (ControlServer, '_') FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST WHERE CollectDate = @Date) ) BEGIN DECLARE @html NVARCHAR(MAX), @table NVARCHAR(MAX) SET @html = N'<html><head><title>Server Level Security Audit Differences</title></head>' + CHAR(10) + N'<body style="font-family: Arial" bgcolor="#FFFFCC">' + N'<h2>Added or Changed</h2>' + N'<table border="1">' + N'<tr>' + N'<th width="120">Server Name</th>' + N'<th width="120">Name</th>' + N'<th width="500">Type Desc</th>' + N'<th width="500">Public</th>' + N'<th width="500">SysAdmin</th>' + N'<th width="500">SecurityAdmin</th>' + N'<th width="500">ServerAdmin</th>' + N'<th width="500">SetupAdmin</th>' + N'<th width="500">ProcessAdmin</th>' + N'<th width="500">DiskAdmin</th>' + N'<th width="500">DBCreator</th>' + N'<th width="500">BulkAdmin</th>' + N'<th width="500">ControlServer</th>' + N'</tr>' + CHAR(10); SELECT @table = CONVERT(varchar(max), (SELECT td = NEW.ServerName, '', td = NEW.Name, '', td = NEW.Type_Desc, '', td = 'Public was ' + ISNULL(OLD.[Public], 'NULL') + ' and Public is now ' + ISNULL(NEW.[Public], 'NULL'), '', td = 'SysAdmin was ' + ISNULL(OLD.SysAdmin, 'NULL') + ' and SysAdmin is now ' + ISNULL(NEW.SysAdmin, 'NULL'), '', td = 'SecurityAdmin was ' + ISNULL(OLD.SecurityAdmin, 'NULL') + ' and SecurityAdmin is now ' + ISNULL(NEW.SecurityAdmin, 'NULL'), '', td = 'ServerAdmin was ' + ISNULL(OLD.ServerAdmin, 'NULL') + ' and ServerAdmin is now ' + ISNULL(NEW.ServerAdmin, 'NULL'), '', td = 'SetupAdmin was ' + ISNULL(OLD.SetupAdmin, 'NULL') + ' and SetupAdmin is now ' + ISNULL(NEW.SetupAdmin, 'NULL'), '', td = 'ProcessAdmin was ' + ISNULL(OLD.ProcessAdmin, 'NULL') + ' and ProcessAdmin is now ' + ISNULL(NEW.ProcessAdmin, 'NULL'), '', td = 'DiskAdmin was ' + ISNULL(OLD.DiskAdmin, 'NULL') + ' and DiskAdmin is now ' + ISNULL(NEW.DiskAdmin, 'NULL'), '', td = 'DBCreator was ' + ISNULL(OLD.DBCreator, 'NULL') + ' and DBCreator is now ' + ISNULL(NEW.DBCreator, 'NULL'), '', td = 'BulkAdmin was ' + ISNULL(OLD.BulkAdmin, 'NULL') + ' and BulkAdmin is now ' + ISNULL(NEW.BulkAdmin, 'NULL'), '', td = 'ControlServer was ' + ISNULL(OLD.ControlServer, 'NULL') + ' and ControlServer is now ' + ISNULL(NEW.ControlServer, 'NULL') FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current NEW LEFT JOIN SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST OLD ON OLD.ServerName = NEW.ServerName WHERE OLD.CollectDate = @Date and ISNULL (new.ServerName, '_') +ISNULL (new.name, '_') +ISNULL (new.Type_Desc, '_') +ISNULL (new.[Public], '_') +ISNULL (new.SysAdmin, '_') +ISNULL (new.SecurityAdmin, '_') +ISNULL (new.ServerAdmin, '_') +ISNULL (new.SetupAdmin, '_') +ISNULL (new.ProcessAdmin, '_') +ISNULL (new.DiskAdmin, '_') +ISNULL (new.DBCreator, '_') +ISNULL (new.BulkAdmin, '_') +ISNULL (new.ControlServer, '_') <> ISNULL (old.ServerName, '_') +ISNULL (old.name, '_') +ISNULL (old.Type_Desc, '_') +ISNULL (old.[Public], '_') +ISNULL (old.SysAdmin, '_') +ISNULL (old.SecurityAdmin, '_') +ISNULL (old.ServerAdmin, '_') +ISNULL (old.SetupAdmin, '_') +ISNULL (old.ProcessAdmin, '_') +ISNULL (old.DiskAdmin, '_') +ISNULL (old.DBCreator, '_') +ISNULL (old.BulkAdmin, '_') +ISNULL (old.ControlServer, '_') FOR XML PATH(N'tr'), TYPE)); SET @html = @html + @table + CHAR(10) + N'</table></body></html>'; EXEC msdb.dbo.sp_send_dbmail @recipients = 'EMAIL@EMAILTEST.com', @body_format = 'HTML', @body= @HTML, @subject = 'Server Level Security Audit Difference - Added or Changed', @profile_name = 'DBA' END --SendMail
比較 OLD 和 NEW 行時，這將獲得任何不同的行或添加：
SELECT NEW.ServerName, NEW.[name], NEW.Type_Desc FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current NEW LEFT JOIN SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST OLD ON OLD.ServerName = NEW.ServerName AND OLD.CollectDate = @Date WHERE ( OLD.ServerName IS NULL OR ( NEW.Public <> OLD.Public OR NEW.SysAdmin <> OLD.SysAdmin OR NEW.SecurityAdmin <> OLD.SecurityAdmin OR NEW.ServerAdmin <> OLD.ServerAdmin OR NEW.SetupAdmin <> OLD.SetupAdmin OR NEW.ProcessAdmin <> OLD.ProcessAdmin OR NEW.DiskAdmin <> OLD.DiskAdmin OR NEW.DBCreator <> OLD.DBCreator OR NEW.BulkAdmin <> OLD.BulkAdmin OR NEW.ControlServer <> OLD.ControlServer ) )