SQL Server me Default Constraint Maintenance System (Auto Fix + Logging + Rollback)

IF OBJECT_ID('dbo.ADT_ConstraintLogs', 'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ADT_ConstraintLogs](
		[LogID] [int] IDENTITY(1,1) PRIMARY KEY,
		[BatchID] [uniqueidentifier] NOT NULL,
		[ExecutedOn] [datetime] NOT NULL,
		[SchemaName] [sysname] NOT NULL,
		[TableName] [sysname] NOT NULL,
		[ColumnName] [sysname] NOT NULL,
		[DataType] [sysname] NOT NULL,
		[IssueType] [varchar](30) NOT NULL,
		[OldConstraint] [sysname] NULL,
		[OldDefault] [nvarchar](4000) NULL,
		[NewConstraint] [nvarchar](128) NOT NULL,
		[NewDefault] [nvarchar](4000) NOT NULL,
		[ApplyScript] [nvarchar](max) NOT NULL,
		[RollbackScript] [nvarchar](max) NOT NULL,
		[Status] [varchar](20) NOT NULL,
		[ErrorMessage] [nvarchar](max) NULL,
		[ExecutedBy] [sysname] NOT NULL,
		[LinkedBatchID] [uniqueidentifier] NULL
	)
END	

IF OBJECT_ID('dbusp_ConstraintMaintenance','P') IS NOT NULL
BEGIN
    DROP PROC dbusp_ConstraintMaintenance
END
GO

-- ============================================================
-- SP: dbusp_ConstraintMaintenance
-- ============================================================
-- Parameters:
--   @TableName  → Specific table ya NULL for all
--   @SchemaName → Default 'dbo'
--   @DryRun     → 1 = sirf report + log, kuch change nahi
--                 0 = actual fix apply karo
--
-- Usage Examples:
--   EXEC dbusp_ConstraintMaintenance @TableName='UserSessions', @DryRun=1
--   EXEC dbusp_ConstraintMaintenance @TableName='UserSessions', @DryRun=0
--   EXEC dbusp_ConstraintMaintenance @DryRun=1   -- all tables
-- ============================================================

CREATE PROCEDURE [dbo].[dbusp_ConstraintMaintenance]
    @TableName  SYSNAME = '',
    @SchemaName SYSNAME = 'dbo',
    @DryRun     BIT     = 1
AS
BEGIN
    SET NOCOUNT ON;

    IF @TableName = '' SET @TableName = NULL;

    -- ─────────────────────────────────────────────────────────────────────
    -- Generate a unique BatchID to group all records from this execution
    -- ─────────────────────────────────────────────────────────────────────
    DECLARE @BatchID UNIQUEIDENTIFIER = NEWID();

    PRINT REPLICATE('=', 65);
    PRINT ' SP  : dbusp_ConstraintMaintenance';
    PRINT ' Batch ID   : ' + CAST(@BatchID AS VARCHAR(36));
    PRINT ' Schema     : ' + @SchemaName;
    PRINT ' Table      : ' + ISNULL(@TableName, '[ALL TABLES]');
    PRINT ' Mode       : ' + CASE @DryRun
                                WHEN 1 THEN 'DRY RUN  — no changes will be applied'
                                ELSE        'LIVE RUN — changes WILL be applied'
                             END;
    PRINT REPLICATE('=', 65);

    -- ─────────────────────────────────────────────────────────────────────
    -- Working table to hold analysis results before any execution
    -- ─────────────────────────────────────────────────────────────────────
    DECLARE @Work TABLE
    (
        RowID           INT IDENTITY(1,1),
        SchemaName      SYSNAME             NOT NULL,
        TableName       SYSNAME             NOT NULL,
        ColumnName      SYSNAME             NOT NULL,
        DataType        SYSNAME             NOT NULL,
        OldConstraint   SYSNAME             NULL,
        OldDefinition   NVARCHAR(4000)      NULL,   -- Raw definition from sys.default_constraints
        NewConstraint   NVARCHAR(128)       NOT NULL,
        FinalDefault    NVARCHAR(4000)      NOT NULL,
        IsNameMismatch  BIT                 NOT NULL,
        IsValueMismatch BIT                 NOT NULL,
        IssueType       VARCHAR(30)         NULL,
        ApplyScript     NVARCHAR(MAX)       NULL,
        RollbackScript  NVARCHAR(MAX)       NULL
    );

    -- ─────────────────────────────────────────────────────────────────────
    -- STEP A: Analyse all eligible columns
    --         Eligible = NOT NULL, non-identity, non-primary-key columns
    -- ─────────────────────────────────────────────────────────────────────
    INSERT INTO @Work
    (
        SchemaName, TableName, ColumnName, DataType,
        OldConstraint, OldDefinition,
        NewConstraint, FinalDefault,
        IsNameMismatch, IsValueMismatch
    )
    SELECT
        s.name          AS SchemaName,
        t.name          AS TableName,
        c.name          AS ColumnName,
        ty.name         AS DataType,
        dc.name         AS OldConstraint,
        dc.definition   AS OldDefinition,

        'DF_' + t.name + '_' + c.name AS NewConstraint,

        -- ── FinalDefault: determine what the default value should be ─────
        --
        -- Rule 1: If an existing default is already a custom (non-standard) value
        --         i.e. not one of (0 / '' / NULL) → preserve it as-is.
        --         SUBSTRING(2, LEN-2) strips only the outer parens that
        --         SQL Server wraps around dc.definition e.g. (getdate()) → getdate()
        --
        -- Rule 2: For standard/missing defaults, assign based on data type:
        --         Numeric  → 0
        --         String   → ''
        --         DateTime → NULL
        --         Others   → NULL
        -- ────────────────────────────────────────────────────────────────
        CASE
            -- Existing custom default: not 0, not '', not NULL → preserve as-is
            WHEN dc.definition IS NOT NULL
                 AND LOWER(REPLACE(REPLACE(dc.definition, '(', ''), ')', ''))
                     NOT IN ('0', '''''', 'null', '')
            THEN
                -- Strip only outer parens: (getdate()) → getdate() | ('Active') → 'Active'
                SUBSTRING(dc.definition, 2, LEN(dc.definition) - 2)

            -- Numeric types: default = 0
            WHEN ty.name IN (
                'int', 'bigint', 'smallint', 'tinyint',
                'bit', 'decimal', 'numeric',
                'float', 'real', 'money', 'smallmoney'
            )
            THEN '0'

            -- String types: default = '' (empty string)
            WHEN ty.name IN (
                'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext'
            )
            THEN ''''''

            -- Date/time types: default = NULL
            WHEN ty.name IN (
                'date', 'datetime', 'datetime2',
                'smalldatetime', 'time', 'datetimeoffset'
            )
            THEN 'NULL'

            -- All other unrecognised types: default = NULL
            ELSE 'NULL'
        END AS FinalDefault,

        -- ── IsNameMismatch: NULL-safe check ──────────────────────────────
        -- If no constraint exists, ISNULL converts NULL to '' so comparison works correctly
        CASE
            WHEN ISNULL(dc.name, '') <> 'DF_' + t.name + '_' + c.name
            THEN 1
            ELSE 0
        END AS IsNameMismatch,

        -- ── IsValueMismatch: does the existing default match the expected? ─
        CASE
            -- No constraint at all → definitely a mismatch
            WHEN dc.definition IS NULL
                THEN 1

            -- Custom (non-standard) value → preserved as-is → no mismatch
            WHEN LOWER(REPLACE(REPLACE(dc.definition, '(', ''), ')', ''))
                 NOT IN ('0', '''''', 'null', '')
                THEN 0

            -- Compare cleaned existing value against expected value
            WHEN LOWER(REPLACE(REPLACE(dc.definition, '(', ''), ')', '')) <>
                 LOWER(
                    CASE
                        WHEN ty.name IN (
                            'int', 'bigint', 'smallint', 'tinyint',
                            'bit', 'decimal', 'numeric',
                            'float', 'real', 'money', 'smallmoney'
                        ) THEN '0'

                        WHEN ty.name IN (
                            'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext'
                        ) THEN ''''''

                        WHEN ty.name IN (
                            'date', 'datetime', 'datetime2',
                            'smalldatetime', 'time', 'datetimeoffset'
                        ) THEN 'null'

                        ELSE 'null'
                    END
                 )
                THEN 1

            ELSE 0
        END AS IsValueMismatch

    FROM sys.tables          t
    JOIN sys.schemas         s   ON  t.schema_id     = s.schema_id
    JOIN sys.columns         c   ON  t.object_id     = c.object_id
    JOIN sys.types           ty  ON  c.user_type_id  = ty.user_type_id
    LEFT JOIN sys.default_constraints dc
                                 ON  dc.parent_object_id = c.object_id
                                AND  dc.parent_column_id = c.column_id
    WHERE
        c.is_nullable   = 0             -- Only NOT NULL columns
        AND c.is_identity = 0           -- Exclude IDENTITY columns
        AND s.name        = @SchemaName
        AND (t.name       = @TableName OR @TableName IS NULL)
        -- Exclude primary key columns
        AND c.column_id NOT IN
        (
            SELECT ic.column_id
            FROM   sys.index_columns ic
            JOIN   sys.indexes       i
                   ON  i.object_id = ic.object_id
                  AND  i.index_id  = ic.index_id
            WHERE  i.is_primary_key = 1
              AND  ic.object_id     = t.object_id
        );

    -- ─────────────────────────────────────────────────────────────────────
    -- STEP B: Set IssueType label for each row
    -- ─────────────────────────────────────────────────────────────────────
    UPDATE @Work
    SET IssueType =
        CASE
            WHEN OldConstraint IS NULL                          THEN 'Missing'
            WHEN IsNameMismatch = 1 AND IsValueMismatch = 1    THEN 'Both'
            WHEN IsNameMismatch = 1                            THEN 'NameMismatch'
            WHEN IsValueMismatch = 1                           THEN 'ValueMismatch'
            ELSE 'OK'
        END;

    -- ─────────────────────────────────────────────────────────────────────
    -- STEP C: Build ApplyScript and RollbackScript for each problem row
    -- ─────────────────────────────────────────────────────────────────────
    UPDATE @Work
    SET
        -- ApplyScript: drop old constraint (if any), then add new one
        ApplyScript =
            CASE
                WHEN OldConstraint IS NOT NULL
                THEN 'ALTER TABLE [' + SchemaName + '].[' + TableName + ']'
                   + ' DROP CONSTRAINT [' + OldConstraint + '];' + CHAR(10)
                ELSE ''
            END
            + 'ALTER TABLE [' + SchemaName + '].[' + TableName + ']'
            + ' ADD CONSTRAINT [' + LEFT(NewConstraint, 128) + ']'
            + ' DEFAULT '   + FinalDefault
            + ' FOR ['      + ColumnName + '];',

        -- RollbackScript: drop the new constraint, restore the old one if it existed
        RollbackScript =
            '/* ROLLBACK: Run this script to undo the fix for ['
            + TableName + '].[' + ColumnName + '] */' + CHAR(10)

            -- Drop the newly added constraint
            + 'ALTER TABLE [' + SchemaName + '].[' + TableName + ']'
            + ' DROP CONSTRAINT IF EXISTS [' + LEFT(NewConstraint, 128) + '];'

            + CASE
                WHEN OldConstraint IS NOT NULL
                -- Restore previous constraint using the original raw definition
                THEN CHAR(10)
                   + 'ALTER TABLE [' + SchemaName + '].[' + TableName + ']'
                   + ' ADD CONSTRAINT [' + OldConstraint + ']'
                   + ' DEFAULT '  + OldDefinition
                   + ' FOR ['     + ColumnName + '];'
                ELSE
                    CHAR(10) + '/* No previous constraint existed — nothing to restore. */'
              END

    WHERE IssueType <> 'OK';

    -- ─────────────────────────────────────────────────────────────────────
    -- STEP D: Print and return the analysis result set
    -- ─────────────────────────────────────────────────────────────────────
    DECLARE @IssueCount INT;
    SELECT @IssueCount = COUNT(*) FROM @Work WHERE IssueType <> 'OK';

    PRINT 'Total issues found: ' + CAST(@IssueCount AS VARCHAR(10));

    -- Return result set regardless of DryRun or Live mode
    SELECT
        SchemaName,
        TableName,
        ColumnName,
        DataType,
        IssueType,
        OldConstraint,
        OldDefinition   AS OldDefault,
        NewConstraint,
        FinalDefault    AS NewDefault,
        ApplyScript,
        RollbackScript
    FROM @Work
    WHERE IssueType <> 'OK'
    ORDER BY SchemaName, TableName, ColumnName;

    IF @IssueCount = 0
    BEGIN
        PRINT 'No issues found. All default constraints are correct.';
        RETURN;
    END;

    -- ─────────────────────────────────────────────────────────────────────
    -- STEP E: DRY RUN — Log with Status = 'DryRun', no DDL executed
    -- ─────────────────────────────────────────────────────────────────────
    IF @DryRun = 1
    BEGIN
        INSERT INTO [dbo].[ADT_ConstraintLogs]
        (
            BatchID, SchemaName, TableName, ColumnName, DataType, IssueType,
            OldConstraint, OldDefault, NewConstraint, NewDefault,
            ApplyScript, RollbackScript, Status
        )
        SELECT
            @BatchID,
            SchemaName, TableName, ColumnName, DataType, IssueType,
            OldConstraint, OldDefinition, NewConstraint, FinalDefault,
            ApplyScript, RollbackScript,
            'DryRun'
        FROM @Work
        WHERE IssueType <> 'OK';

        PRINT '';
        PRINT 'DRY RUN complete. No changes were applied.';
        PRINT CAST(@IssueCount AS VARCHAR(10)) + ' issue(s) logged with Status = DryRun.';
        PRINT 'Batch ID : ' + CAST(@BatchID AS VARCHAR(36));
        PRINT '';
        PRINT 'Review  : SELECT * FROM ADT_ConstraintLogs WHERE BatchID = '''
              + CAST(@BatchID AS VARCHAR(36)) + '''';
        PRINT 'Go Live : EXEC dbusp_ConstraintMaintenance'
              + ' @TableName=''' + ISNULL(@TableName, '') + ''','
              + ' @SchemaName=''' + @SchemaName + ''','
              + ' @DryRun=0';
        RETURN;
    END;

    -- ─────────────────────────────────────────────────────────────────────
    -- STEP E-2: Mark all previous DryRun records for this table as Superseded
    --           This prevents confusion when multiple dry runs exist
    -- ─────────────────────────────────────────────────────────────────────
    UPDATE [dbo].[ADT_ConstraintLogs]
    SET    Status        = 'Superseded',
           LinkedBatchID = @BatchID         -- Point to current Live BatchID
    WHERE  Status        = 'DryRun'
      AND  SchemaName    = @SchemaName
      AND  (TableName    = @TableName OR @TableName IS NULL);

    PRINT CAST(@@ROWCOUNT AS VARCHAR(10))
        + ' previous DryRun record(s) marked as Superseded.';

    -- ─────────────────────────────────────────────────────────────────────
    -- STEP F: LIVE RUN — Execute each fix with TRY/CATCH + per-row transaction
    --         Each row is an independent transaction so one failure
    --         does not block other rows from being processed.
    -- ─────────────────────────────────────────────────────────────────────
    DECLARE
        @C_Schema       SYSNAME,
        @C_Table        SYSNAME,
        @C_Column       SYSNAME,
        @C_DataType     SYSNAME,
        @C_OldConst     SYSNAME,
        @C_OldDef       NVARCHAR(4000),
        @C_NewConst     NVARCHAR(128),
        @C_FinalDef     NVARCHAR(4000),
        @C_ApplyScript  NVARCHAR(MAX),
        @C_RollScript   NVARCHAR(MAX),
        @C_IssueType    VARCHAR(30),
        @ExecSQL        NVARCHAR(MAX),
        @LogID          INT,
        @SuccessCount   INT = 0,
        @FailCount      INT = 0;

    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT
            SchemaName, TableName, ColumnName, DataType,
            OldConstraint, OldDefinition,
            NewConstraint, FinalDefault,
            ApplyScript, RollbackScript, IssueType
        FROM @Work
        WHERE IssueType <> 'OK'
        ORDER BY SchemaName, TableName, ColumnName;

    OPEN cur;

    FETCH NEXT FROM cur INTO
        @C_Schema, @C_Table, @C_Column, @C_DataType,
        @C_OldConst, @C_OldDef,
        @C_NewConst, @C_FinalDef,
        @C_ApplyScript, @C_RollScript, @C_IssueType;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Insert a Pending log record before executing,
        -- so we have a record even if something goes critically wrong
        INSERT INTO [dbo].[ADT_ConstraintLogs]
        (
            BatchID, SchemaName, TableName, ColumnName, DataType, IssueType,
            OldConstraint, OldDefault, NewConstraint, NewDefault,
            ApplyScript, RollbackScript, Status
        )
        VALUES
        (
            @BatchID, @C_Schema, @C_Table, @C_Column, @C_DataType, @C_IssueType,
            @C_OldConst, @C_OldDef, @C_NewConst, @C_FinalDef,
            @C_ApplyScript, @C_RollScript, 'Pending'
        );

        SET @LogID = SCOPE_IDENTITY();

        BEGIN TRY
            BEGIN TRANSACTION;

                -- Drop old constraint only if one existed
                IF @C_OldConst IS NOT NULL
                BEGIN
                    SET @ExecSQL =
                        'ALTER TABLE [' + @C_Schema + '].[' + @C_Table + ']'
                        + ' DROP CONSTRAINT [' + @C_OldConst + '];';
                    PRINT 'DROP  → ' + @ExecSQL;
                    EXEC sp_executesql @ExecSQL;
                END;

                -- Add the correctly named constraint with the correct default value
                SET @ExecSQL =
                    'ALTER TABLE [' + @C_Schema + '].[' + @C_Table + ']'
                    + ' ADD CONSTRAINT [' + LEFT(@C_NewConst, 128) + ']'
                    + ' DEFAULT '  + @C_FinalDef
                    + ' FOR ['     + @C_Column + '];';
                PRINT 'ADD   → ' + @ExecSQL;
                EXEC sp_executesql @ExecSQL;

            COMMIT TRANSACTION;

            -- Update log record to Success
            UPDATE [dbo].[ADT_ConstraintLogs]
            SET    Status = 'Success'
            WHERE  LogID  = @LogID;

            SET @SuccessCount += 1;
            PRINT 'OK    → [' + @C_Table + '].[' + @C_Column + ']';

        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

            -- Update log record to Failed with error details
            UPDATE [dbo].[ADT_ConstraintLogs]
            SET    Status       = 'Failed',
                   ErrorMessage = ERROR_MESSAGE()
            WHERE  LogID = @LogID;

            SET @FailCount += 1;
            PRINT 'FAIL  → [' + @C_Table + '].[' + @C_Column + ']'
                + '  Error: ' + ERROR_MESSAGE();
        END CATCH;

        FETCH NEXT FROM cur INTO
            @C_Schema, @C_Table, @C_Column, @C_DataType,
            @C_OldConst, @C_OldDef,
            @C_NewConst, @C_FinalDef,
            @C_ApplyScript, @C_RollScript, @C_IssueType;
    END;

    CLOSE cur;
    DEALLOCATE cur;

    -- ─────────────────────────────────────────────────────────────────────
    -- STEP G: Print final summary
    -- ─────────────────────────────────────────────────────────────────────
    PRINT '';
    PRINT REPLICATE('=', 65);
    PRINT ' EXECUTION SUMMARY';
    PRINT '  Success  : ' + CAST(@SuccessCount AS VARCHAR(10));
    PRINT '  Failed   : ' + CAST(@FailCount   AS VARCHAR(10));
    PRINT '  Batch ID : ' + CAST(@BatchID     AS VARCHAR(36));
    PRINT REPLICATE('=', 65);
    PRINT ' To rollback any successful fix, run:';
    PRINT '   SELECT RollbackScript FROM ADT_ConstraintLogs';
    PRINT '   WHERE BatchID = ''' + CAST(@BatchID AS VARCHAR(36))
        + ''' AND Status = ''Success''';
    PRINT REPLICATE('=', 65);

    -- Return status summary as a result set
    SELECT
        Status,
        COUNT(*) AS [Count]
    FROM  [dbo].[ADT_ConstraintLogs]
    WHERE  BatchID = @BatchID
    GROUP BY Status
    ORDER BY Status;

END;
GO

EXEC dbusp_ConstraintMaintenance @DryRun = 1

EXEC dbusp_ConstraintMaintenance @DryRun = 0

SELECT RollbackScript 
FROM ADT_ConstraintLogs
WHERE Status = 'Success'