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

Convert HTML Tables To DataSet In C#

private DataSet ConvertHTMLTablesToDataSet(string HTML) {
        // Declarations
        DataSet ds = new DataSet();
        DataTable dt = null;
        DataRow dr = null;
        DataColumn dc = null;
        string TableExpression = "<table[^>]*>(.*?)</table>";
        string HeaderExpression = "<th[^>]*>(.*?)</th>";
        string RowExpression = "<tr[^>]*>(.*?)</tr>";
        string ColumnExpression = "<td[^>]*>(.*?)</td>";
        bool HeadersExist = false;
        int iCurrentColumn = 0;
        int iCurrentRow = 0;
        // Get a match for all the tables in the HTML
        MatchCollection Tables = Regex.Matches(HTML, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
        // Loop through each table element
        foreach(Match Table in Tables) {
            // Reset the current row counter and the header flag
            iCurrentRow = 0;
            HeadersExist = false;
            // Add a new table to the DataSet
            dt = new DataTable();
            //Create the relevant amount of columns for this table (use the headers if they exist, otherwise use default names)
            // if (Table.Value.Contains("<th"))
            if (Table.Value.Contains("<th")) {
                // Set the HeadersExist flag
                HeadersExist = true;
                // Get a match for all the rows in the table
                MatchCollection Headers = Regex.Matches(Table.Value, HeaderExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
                // Loop through each header element
                foreach(Match Header in Headers) {
                    dt.Columns.Add(Header.Groups[1].ToString());
                }
            } else {
                for (int iColumns = 1; iColumns <= Regex.Matches(Regex.Matches(Regex.Matches(Table.Value, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase).Count; iColumns++) {
                    dt.Columns.Add("Column " + iColumns);
                }
            }
            //Get a match for all the rows in the table
            MatchCollection Rows = Regex.Matches(Table.Value, RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
            // Loop through each row element
            foreach(Match Row in Rows) {
                    // Only loop through the row if it isn't a header row
                    if (!(iCurrentRow == 0 && HeadersExist)) {
                        // Create a new row and reset the current column counter
                        dr = dt.NewRow();
                        iCurrentColumn = 0;
                        // Get a match for all the columns in the row
                        MatchCollection Columns = Regex.Matches(Row.Value, ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
                        // Loop through each column element
                        foreach(Match Column in Columns) {
                                // Add the value to the DataRow
                                dr[iCurrentColumn] = Column.Groups[1].ToString();
                                // Increase the current column
                                iCurrentColumn++;
                            }
                            // Add the DataRow to the DataTable
                        dt.Rows.Add(dr);
                    }
                    // Increase the current row counter
                    iCurrentRow++;
                }
                // Add the DataTable to the DataSet
            ds.Tables.Add(dt);
        }
        return ds;
    }