Truncate tables where Referential Integrity exists

--=====================================================================================--
--=== Author:Perry D J Whittle===--
--=== Date:13/07/2023===--
--=== SynopsisThis 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:===--
--=== PDJWV1.009/03/2023Initial script creation===--
--=== PDJWV1.517/07/2023Script did not cater for composite FKs so added ===--
--=== PDJWV1.725/07/2023Tidy up the composite key script detail===--
--=== PDJWV1.822/03/2024Further 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