SQL SERVER DROP, ADD, DISABLE AND ENABLE Foreign Key contraints: (edited)

drop-disabled-enabled-fk-table-constraints.sql

​
-- Drop FK constraints
BEGIN
    ALTER TABLE [Report].DailyOccupancyStats_Agg DROP CONSTRAINT [FK_Report.DailyOccupancyStats_Agg_DateId];
    ALTER TABLE [Report].OccupancySessionsStats_Agg DROP CONSTRAINT [FK_Report.OccupancySessionsStats_Agg_DateId];
    ALTER TABLE [Report].OccupancySessionsOspStats_Agg DROP CONSTRAINT [FK_Report.OccupancySessionsOspStats_Agg_DateId];
    ALTER TABLE [Report].ParkeonSessionsStats_Agg DROP CONSTRAINT [FK_Report.ParkeonSessionsStats_Agg_DateId];
    ALTER TABLE [Report].ParkingSessionsStats_Agg DROP CONSTRAINT [FK_Report.ParkingSessionsStats_Agg_DateId];
    ALTER TABLE [Report].ParkingSessionsOspStats_Agg DROP CONSTRAINT [FK_Report.ParkingSessionsOspStats_Agg_DateId];
    ALTER TABLE [Report].ParkingUsersCreationStats_Agg DROP CONSTRAINT [FK_Report.ParkingUsersCreationStats_Agg_CreationDateId]
    ALTER TABLE [Report].PaymentOrderTransactionsStats_Agg DROP CONSTRAINT [FK_Report.PaymentOrderTransactionsStats_Agg_DateId]
    ALTER TABLE [Report].PenaltyChargeNotices_Origin_Agg DROP CONSTRAINT [FK_Report.PenaltyChargeNotices_Origin_Agg_DateId]
    ALTER TABLE [Report].UserSessionRetentionStats DROP CONSTRAINT [FK_Report.UserSessionRetentionStats_DateId]
END
​
​
-- Add back FK constraints
BEGIN
    ALTER TABLE [Report].[DailyOccupancyStats_Agg] ADD CONSTRAINT [FK_Report.DailyOccupancyStats_Agg_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[OccupancySessionsStats_Agg] ADD CONSTRAINT [FK_Report.OccupancySessionsStats_Agg_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[OccupancySessionsOspStats_Agg] ADD CONSTRAINT [FK_Report.OccupancySessionsOspStats_Agg_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[ParkeonSessionsStats_Agg] ADD CONSTRAINT [FK_Report.ParkeonSessionsStats_Agg_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[ParkingSessionsStats_Agg] ADD CONSTRAINT [FK_Report.ParkingSessionsStats_Agg_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[ParkingSessionsOspStats_Agg] ADD CONSTRAINT [FK_Report.ParkingSessionsOspStats_Agg_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[ParkingUsersCreationStats_Agg] ADD CONSTRAINT [FK_Report.ParkingUsersCreationStats_Agg_CreationDateId] FOREIGN KEY([CreationDateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[PaymentOrderTransactionsStats_Agg] ADD CONSTRAINT [FK_Report.PaymentOrderTransactionsStats_Agg_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[PenaltyChargeNotices_Origin_Agg] ADD CONSTRAINT [FK_Report.PenaltyChargeNotices_Origin_Agg_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
    ALTER TABLE [Report].[UserSessionRetentionStats] ADD CONSTRAINT [FK_Report.UserSessionRetentionStats_DateId] FOREIGN KEY([DateId]) REFERENCES [Report].[DimDate] ([DateId])
END
​
-- Disable table constraints
BEGIN
    ALTER TABLE [Report].DailyOccupancyStats_Agg NOCHECK CONSTRAINT [FK_Report.DailyOccupancyStats_Agg_DateId]        
    ALTER TABLE [Report].OccupancySessionsStats_Agg NOCHECK CONSTRAINT [FK_Report.OccupancySessionsStats_Agg_DateId]
    ALTER TABLE [Report].OccupancySessionsOspStats_Agg NOCHECK CONSTRAINT [FK_Report.OccupancySessionsOspStats_Agg_DateId]    
    ALTER TABLE [Report].ParkeonSessionsStats_Agg NOCHECK CONSTRAINT [FK_Report.ParkeonSessionsStats_Agg_DateId]
    ALTER TABLE [Report].ParkingSessionsStats_Agg NOCHECK CONSTRAINT [FK_Report.ParkingSessionsStats_Agg_DateId]
    ALTER TABLE [Report].ParkingSessionsOspStats_Agg NOCHECK CONSTRAINT [FK_Report.ParkingSessionsOspStats_Agg_DateId]
    ALTER TABLE [Report].ParkingUsersCreationStats_Agg NOCHECK CONSTRAINT [FK_Report.ParkingUsersCreationStats_Agg_CreationDateId]
    ALTER TABLE [Report].PaymentOrderTransactionsStats_Agg NOCHECK CONSTRAINT [FK_Report.PaymentOrderTransactionsStats_Agg_DateId]
    ALTER TABLE [Report].PenaltyChargeNotices_Origin_Agg NOCHECK CONSTRAINT [FK_Report.PenaltyChargeNotices_Origin_Agg_DateId]
    ALTER TABLE [Report].UserSessionRetentionStats NOCHECK CONSTRAINT [FK_Report.UserSessionRetentionStats_DateId]
END
​
-- Enable table constraints
BEGIN
    ALTER TABLE [Report].DailyOccupancyStats_Agg CHECK CONSTRAINT [FK_Report.DailyOccupancyStats_Agg_DateId]
    ALTER TABLE [Report].OccupancySessionsStats_Agg CHECK CONSTRAINT [FK_Report.OccupancySessionsStats_Agg_DateId]
    ALTER TABLE [Report].OccupancySessionsOspStats_Agg CHECK CONSTRAINT [FK_Report.OccupancySessionsOspStats_Agg_DateId]
    ALTER TABLE [Report].ParkeonSessionsStats_Agg CHECK CONSTRAINT [FK_Report.ParkeonSessionsStats_Agg_DateId]    
    ALTER TABLE [Report].ParkingSessionsOspStats_Agg CHECK CONSTRAINT [FK_Report.ParkingSessionsOspStats_Agg_DateId]
    ALTER TABLE [Report].ParkingSessionsStats_Agg CHECK CONSTRAINT [FK_Report.ParkingSessionsStats_Agg_DateId]
    ALTER TABLE [Report].ParkingUsersCreationStats_Agg CHECK CONSTRAINT [FK_Report.ParkingUsersCreationStats_Agg_CreationDateId]    
    ALTER TABLE [Report].PaymentOrderTransactionsStats_Agg CHECK CONSTRAINT [FK_Report.PaymentOrderTransactionsStats_Agg_DateId]
    ALTER TABLE [Report].PenaltyChargeNotices_Origin_Agg CHECK CONSTRAINT [FK_Report.PenaltyChargeNotices_Origin_Agg_DateId]
    ALTER TABLE [Report].UserSessionRetentionStats CHECK CONSTRAINT [FK_Report.UserSessionRetentionStats_DateId]
END
​
-- Show table relations by FK
WITH TableRelations 
    AS (
        select schema_name(tab.schema_id) + '.' + tab.name as [table],
            col.column_id AS column_id,
            col.name as column_name,
            case when fk.object_id is not null then '>-' else null end as rel,
            schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
            pk_col.name as pk_column_name,
            fk_cols.constraint_column_id as no,
            fk.name as fk_constraint_name,
            case when fk.is_disabled = 0 
                then 'Active' 
                else 'Disabled' 
                end as [status]
        from sys.tables tab
            inner join sys.columns col 
                on col.object_id = tab.object_id
            left outer join sys.foreign_key_columns fk_cols
                on fk_cols.parent_object_id = tab.object_id
                and fk_cols.parent_column_id = col.column_id
            left outer join sys.foreign_keys fk
                on fk.object_id = fk_cols.constraint_object_id
            left outer join sys.tables pk_tab
                on pk_tab.object_id = fk_cols.referenced_object_id
            left outer join sys.columns pk_col
                on pk_col.column_id = fk_cols.referenced_column_id
                and pk_col.object_id = fk_cols.referenced_object_id            
    )
​
SELECT * FROM TableRelations
WHERE primary_table = 'Report.DimDate'
ORDER BY primary_table, column_id