How To Track Database Changes

CREATE DATABASE Audit


USE [Audit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[RunDbAudit]

(
    @DataBaseName VARCHAR(100)
)
 
AS
BEGIN


DECLARE @AuditDbName VARCHAR(100);
DECLARE @BaseTable VARCHAR(100);
DECLARE @SqlCmd NVARCHAR(MAX);
DECLARE @ParamDef NVARCHAR(MAX);

SET @BaseTable =  '__DB__'+ @DataBaseName ;
SET @AuditDbName = DB_NAME();




DECLARE @AuditExcludedObjects NVARCHAR(MAX);
SET @AuditExcludedObjects = 'fn_diagramobjects,sysdiagrams,'+@BaseTable+'';

DECLARE @BaseTableCount INT;

SET @SqlCmd = 'SELECT @BaseTableCount = COUNT(*)  FROM '+@AuditDbName+'.INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_CATALOG = @AuditDbName
                 AND  TABLE_NAME = @BaseTable';
SET @ParamDef = '@BaseTable VARCHAR(100), @DataBaseName VARCHAR(100), @AuditDbName VARCHAR(100), @BaseTableCount INT OUTPUT';

                 EXECUTE sp_executesql @SqlCmd,
                 @ParamDef,
                 @BaseTable = @BaseTable, @DataBaseName = @DataBaseName, @AuditDbName = @AuditDbName, @BaseTableCount = @BaseTableCount OUTPUT;


--SELECT @SqlCmd;
--SELECT @BaseTableCount;

IF (@BaseTableCount = 0)
BEGIN

    SET @SqlCmd = '
    SELECT    
    @DataBaseName AS [DB_NAME]
    , name AS [OBJECT_NAME]
    , object_id AS [OBJECT_ID]
    , type AS [OBJECT_TYPE]
    , type_desc AS [TYPE_DESC]
    , create_date AS [CREATED_ON]
    , modify_date AS [MODIFIED_ON]
    , CONVERT(DATETIME,NULL) AS [DELETED_ON]
    , CONVERT(BIT,0) AS [MASTER_TABLE]
    , CONVERT(BIT,0) AS [AUDIT_EXCLUDED]
    , CONVERT(VARCHAR(MAX),'''') AS [CHANGE_INFO]
    , CONVERT(DECIMAL(18,2), 1.00) AS [VERSION]
    INTO [dbo].['+@BaseTable+']
    FROM ['+@DataBaseName+'].sys.objects WHERE type in (''P'',''U'',''FN'',''V'');

    UPDATE [dbo].['+@BaseTable+'] SET [AUDIT_EXCLUDED] = 1 WHERE [OBJECT_NAME] IN (@AuditExcludedObjects)
    ';


    SET @ParamDef = '@AuditExcludedObjects NVARCHAR(MAX), @DataBaseName VARCHAR(100)';
    EXECUTE sp_executesql @SqlCmd,
    @ParamDef,
    @AuditExcludedObjects = @AuditExcludedObjects, @DataBaseName = @DataBaseName
    ;

END
ELSE
BEGIN

------------------------------------------------------------ Audit--------------------------------------------------------------

SET @SqlCmd = '

DECLARE @PrevVer DECIMAL(18,2);
DECLARE @PrevVerDt DATETIME;

DECLARE @NextVer DECIMAL(18,2);
DECLARE @NextVerDt DATETIME;

SET @NextVerDt = GETDATE();
SET @PrevVerDt = (SELECT MAX(MODIFIED_ON) FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED] = 0);
SET @PrevVer = (SELECT MAX([VERSION]) FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED] = 0);


SET @NextVer = @PrevVer + 0.01;

IF OBJECT_ID(''tempdb..#temp__DB_OBJECT'') IS NOT NULL DROP TABLE #temp__DB_OBJECT

      SELECT    
      @DataBaseName AS [DB_NAME]
    , name AS [OBJECT_NAME]
    , object_id AS [OBJECT_ID]
    , type AS [OBJECT_TYPE]
    , type_desc AS [TYPE_DESC]
    , create_date AS [CREATED_ON]
    , modify_date AS [MODIFIED_ON]
    , CONVERT(DATETIME,NULL) AS [DELETED_ON]
    , CONVERT(BIT,0) AS [MASTER_TABLE]
    , CONVERT(BIT,0) AS [AUDIT_EXCLUDED]
    , CONVERT(VARCHAR(MAX),'''') AS [CHANGE_INFO]
    , @NextVer AS [VERSION]
    INTO #temp__DB_OBJECT
    FROM ['+@DataBaseName+'].sys.objects WHERE type in (''P'',''U'',''FN'',''V'')
    AND name NOT IN ((SELECT [OBJECT_NAME] FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED]=1))


    UPDATE P
    SET P.MODIFIED_ON = tmp.MODIFIED_ON, P.[VERSION] = tmp.[VERSION]
    FROM [dbo].['+@BaseTable+'] P
    INNER JOIN #temp__DB_OBJECT tmp ON
    (P.[OBJECT_NAME] = tmp.[OBJECT_NAME] AND P.TYPE_DESC = tmp.TYPE_DESC)
    WHERE P.AUDIT_EXCLUDED = 0
    AND P.MODIFIED_ON <> tmp.MODIFIED_ON



    INSERT INTO [dbo].['+@BaseTable+']
    SELECT t.* FROM #temp__DB_OBJECT t
    LEFT OUTER JOIN [dbo].['+@BaseTable+'] DO ON t.[OBJECT_ID] = DO.[OBJECT_ID]
    WHERE DO.[OBJECT_NAME] IS NULL



    UPDATE P
     SET P.DELETED_ON = @NextVerDt, P.[VERSION] = @NextVer
    FROM [dbo].['+@BaseTable+'] P
    INNER JOIN (
        SELECT
        DO.[OBJECT_NAME],
        DO.[OBJECT_ID],
        DO.[OBJECT_TYPE],
        DO.[TYPE_DESC]
        FROM  [dbo].['+@BaseTable+'] DO
        LEFT OUTER JOIN #temp__DB_OBJECT t ON t.[OBJECT_ID] = DO.[OBJECT_ID]
        WHERE t.[OBJECT_NAME] IS NULL AND DO.AUDIT_EXCLUDED = 0
    ) Q ON (P.[OBJECT_NAME] = Q.[OBJECT_NAME] AND P.OBJECT_TYPE = Q.OBJECT_TYPE AND P.TYPE_DESC = Q.TYPE_DESC)
    WHERE P.[DELETED_ON] IS NULL AND P.AUDIT_EXCLUDED = 0


    DECLARE @CurrentVer DECIMAL(18,2);
    SET @CurrentVer = (SELECT MAX(VERSION) FROM [dbo].['+@BaseTable+'] WHERE AUDIT_EXCLUDED = 0)

    SELECT CASE WHEN @CurrentVer = @NextVer
        THEN ''Current Database Version : '' + CONVERT(VARCHAR,@CurrentVer)  + ''  :: Changes info given below.''
        ELSE ''Current Database Version : '' + CONVERT(VARCHAR,@CurrentVer) + ''  :: Changes in last version given below''
        END AS [Message];

    SELECT * FROM [dbo].['+@BaseTable+'] WHERE [VERSION] = @CurrentVer;


IF OBJECT_ID(''tempdb..#temp__DB_OBJECT'') IS NOT NULL DROP TABLE #temp__DB_OBJECT
';


SET @ParamDef = '@DataBaseName VARCHAR(100)';

EXECUTE sp_executesql @SqlCmd
,@ParamDef
,@DataBaseName = @DataBaseName;


------------------------------------------------------------/Audit--------------------------------------------------------------
END

   


END


exec RunDbAudit 'DATABASE NAME' --RUN 2 TIMES FOR 1st RUN