Alle tabellen in een database leegmaken en de foreign keys behouden: een compleet script

1
DECLARE @SchemaName NVARCHAR(128) = 'your_schema_name';
2
DECLARE @SQL NVARCHAR(MAX);
3

4
-- Step 1: Create a temporary table to store constraints
5
IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
6
    DROP TABLE #ForeignKeys;
7

8
CREATE TABLE #ForeignKeys (
9
    TableName NVARCHAR(128),
10
    ConstraintName NVARCHAR(128),
11
    ForeignKeyDefinition NVARCHAR(MAX)
12
);
13

14
-- Step 2: Populate the temporary table with foreign keys in the schema
15
INSERT INTO #ForeignKeys (TableName, ConstraintName, ForeignKeyDefinition)
16
SELECT
17
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
18
    fk.name AS ConstraintName,
19
    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) +
20
    ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' +
21
    STUFF((
22
        SELECT ', ' + QUOTENAME(c.name)
23
        FROM sys.columns c
24
        INNER JOIN sys.foreign_key_columns fkc
25
            ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.object_id
26
        WHERE fkc.constraint_object_id = fk.object_id
27
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') +
28
    ') REFERENCES ' + QUOTENAME(SCHEMA_NAME(rt.schema_id)) + '.' + QUOTENAME(rt.name) +
29
    '(' +
30
    STUFF((
31
        SELECT ', ' + QUOTENAME(rc.name)
32
        FROM sys.columns rc
33
        INNER JOIN sys.foreign_key_columns rfkc
34
            ON rfkc.referenced_column_id = rc.column_id AND rfkc.referenced_object_id = rc.object_id
35
        WHERE rfkc.constraint_object_id = fk.object_id
36
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' +
37
    CASE
38
        WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
39
        WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
40
        ELSE ''
41
    END
42
FROM sys.foreign_keys fk
43
INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
44
INNER JOIN sys.tables rt ON fk.referenced_object_id = rt.object_id
45
WHERE SCHEMA_NAME(t.schema_id) = @SchemaName;
46

47
-- Step 3: Drop all foreign keys using XML-based string concatenation
48
SELECT @SQL =
49
    (SELECT 'ALTER TABLE ' + TableName + ' DROP CONSTRAINT ' + QUOTENAME(ConstraintName) + ';' + CHAR(13)
50
     FROM #ForeignKeys
51
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');
52

53
IF @SQL IS NOT NULL
54
    EXEC sp_executesql @SQL;
55

56
-- Step 4: Truncate all tables in the schema in reverse dependency order
57
SELECT @SQL =
58
    (SELECT 'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + ';' + CHAR(13)
59
     FROM sys.tables t
60
     WHERE SCHEMA_NAME(t.schema_id) = @SchemaName
61
     ORDER BY t.create_date DESC
62
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');
63

64
IF @SQL IS NOT NULL
65
    EXEC sp_executesql @SQL;
66

67
-- Step 5: Restore the foreign keys using XML-based string concatenation
68
SELECT @SQL =
69
    (SELECT ForeignKeyDefinition + CHAR(13)
70
     FROM #ForeignKeys
71
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');
72

73
IF @SQL IS NOT NULL
74
    EXEC sp_executesql @SQL;
75

76
-- Cleanup
77
DROP TABLE #ForeignKeys;
78

79
PRINT 'All tables have been truncated, and constraints have been restored.';