Combining Silver Tables into a Model – Dat a Engineering with Fabric (2024-05-15)

--=====================================================================================--
--=== Author: Perry D J Whittle ===--
--=== Date: 13/07/2023 ===--
--=== Synopsis This script will generate drop and create statements for all FK ===--
--=== constraints in the selected database. This will provide the ability ===--
--=== to truncate any tables rather than run delete statements which will ===--
--=== take some time on large tables. ===--
--=== ===--
--=== Changelog: ===--
--=== PDJW V1.0 09/03/2023 Initial script creation ===--
--=== PDJW V1.5 17/07/2023 Script did not cater for composite FKs so added ===--
--=== PDJW V1.7 25/07/2023 Tidy up the composite key script detail ===--
--=== PDJW V1.8 22/03/2024 Further tidy up the composite key detail ===--
--=====================================================================================--

--============================================================--
--=== Execute under the context of the db you're working on ===--
--============================================================--
--USE [somedb]
--GO

--==========================--
--=== Suppress rowcounts ===--
--==========================--
SET NOCOUNT ON

--==========================================--
--=== Gen the drop constraint statements ===--
--==========================================--
PRINT '--!!! The following statements run first to remove the referential integrity !!!--'
SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(name) + ';' + CHAR(13)
FROM sys.foreign_keys
ORDER BY QUOTENAME(OBJECT_NAME(parent_object_id))

--================================--
--=== Now truncate your tables ===--
--================================--
PRINT CHAR(10) + '--!!! Now truncate your tables before continuing !!!--' + CHAR(10) + CHAR(13)

--============================================--
--=== Gen the create constraint statements ===--
--============================================--
PRINT '--!!! The following statements run last to restore the referential integrity !!!--'
SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(fk_tab.schema_id)) + '.' + QUOTENAME(fk_tab.name) + ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(fk.name) +
' FOREIGN KEY(' +
STUFF((SELECT ', ' + QUOTENAME(c2.name)
FROM sys.foreign_key_columns fkc2
INNER JOIN sys.columns c2 ON fkc2.parent_object_id=c2.object_id
AND fkc2.parent_column_id=c2.column_id
WHERE fkc2.parent_object_id = fk_tab.object_id
GROUP BY c2.name
FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'')
+ ')
REFERENCES ' + QUOTENAME(SCHEMA_NAME(pk_tab.schema_id)) + '.' + QUOTENAME(pk_tab.name) +
' (' +
STUFF((SELECT ', ' + QUOTENAME(c3.name)
FROM sys.foreign_key_columns fkc3
INNER JOIN sys.columns c3 ON fkc3.referenced_object_id=c3.object_id
AND fkc3.referenced_column_id=c3.column_id
WHERE fkc3.referenced_object_id = pk_tab.object_id
GROUP BY c3.name
FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'')
+ ')
GO
ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(fk.schema_id)) + '.' + QUOTENAME(fk_tab.name) + ' CHECK CONSTRAINT ' + QUOTENAME(fk.name) + '
GO' + CHAR(13)
FROM sys.foreign_keys fk
INNER JOIN sys.tables fk_tab on fk.parent_object_id=fk_tab.object_id
INNER JOIN sys.tables pk_tab on fk.referenced_object_id=pk_tab.object_id
ORDER BY fk_tab.name