Run the commented out section once, populate the _TruncateList table with the tables you want truncated, then run the rest of the script. The _ScriptLog table will need to be cleaned up over time if you do this a lot.
You can modify this if you want to do all tables, just put in SELECT name INTO #TruncateList FROM sys.tables. However, you usually don't want to do them all.
Also, this will affect all foreign keys in the database, and you can modify that as well if it's too blunt-force for your application. It's not for my purposes.
IF OBJECT_ID('TempDB..
DROP TABLE
END
IF OBJECT_ID('TempDB..#TruncateList') IS NOT NULL BEGIN
DROP TABLE
END
IF OBJECT_ID('TempDB..#CreateFK') IS NOT NULL BEGIN
DROP TABLE
END
SELECT Scripts = 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP CONSTRAINT ' + '[' + f.name + ']'
INTO
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
SELECT TableName
INTO
FROM _TruncateList
SELECT Scripts = 'ALTER TABLE ' + const.parent_obj + '
ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
' + const.parent_col_csv + '
) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
'
INTO
FROM (
SELECT QUOTENAME(fk.NAME) AS [const_name]
,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
FROM sys.foreign_key_columns AS fcP
WHERE fcp.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [parent_col_csv]
,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
FROM sys.foreign_key_columns AS fcR
WHERE fcR.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [ref_col_csv]
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
GROUP BY fkc.parent_object_id
,fkc.referenced_object_id
,fk.NAME
,fk.object_id
,schParent.NAME
,schRef.NAME
) AS const
ORDER BY const.const_name
INSERT INTO _ScriptLog (Script)
SELECT Scripts
FROM
DECLARE @Cmd NVarChar(4000)
, @TableName SysName
WHILE 0 < (SELECT Count(1) FROM
SELECT TOP 1 @Cmd = Scripts
FROM
EXEC (@Cmd)
DELETE
END
WHILE 0 < (SELECT Count(1) FROM
SELECT TOP 1 @Cmd = N'TRUNCATE TABLE ' + TableName
, @TableName = TableName
FROM
EXEC (@Cmd)
DELETE
END
WHILE 0 < (SELECT Count(1) FROM
SELECT TOP 1 @Cmd = Scripts
FROM
EXEC (@Cmd)
DELETE
END