This is my set up: I'm using SQL server on my Windows Pc and I have a server with SQL Server over Windows Server...

The point here is that I need to develop a procedure to truncate all the table in my DB at any given time... To do that on my large DB (still in developing and growing) I need a query that can find all the foreign key dependency hierarchy so that I can truncate all the table in the list given by the query... I find that recursive query online but the point is that the query execute on the server but not on localhost... The query need a recursion level of 5 (max) but even if I specify it in the query (option (maxrecursion 100)) it doesn't execute locally...

This is the query:

WITH dependencies -- Get object with FK dependencies
AS (
    SELECT FK.TABLE_NAME AS Obj
        , PK.TABLE_NAME AS Depends
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    ), 
no_dependencies -- The first level are objects with no dependencies 
AS (
    SELECT 
        name AS Obj
    FROM sys.objects
    WHERE name NOT IN (SELECT obj FROM dependencies) --we remove objects with dependencies from first CTE
    AND type = 'U' -- Just tables
    ), 
recursiv -- recursive CTE to get dependencies
AS (
    SELECT Obj AS [Table]
        , CAST('' AS VARCHAR(max)) AS DependsON
        , 0 AS LVL -- Level 0 indicate tables with no dependencies
    FROM no_dependencies
 
    UNION ALL
 
    SELECT d.Obj AS [Table]
        , CAST(IIF(LVL > 0, r.DependsON   ' > ', '')   d.Depends AS VARCHAR(max)) -- visually reflects hierarchy
        , R.lvl   1 AS LVL
    FROM dependencies d
    INNER JOIN recursiv r
        ON d.Depends = r.[Table]
    )
-- The final result, with some extra fields for more information
SELECT DISTINCT SCHEMA_NAME(O.schema_id) AS [TableSchema]
    , R.[Table]
    , R.DependsON
    , R.LVL
FROM recursiv R
INNER JOIN sys.objects O
    ON R.[Table] = O.name
ORDER BY R.LVL
    , R.[Table]

option (maxrecursion 100);

CodePudding user response：

I don't know the reason about why this is happening to you... What I can suggest is to follow these steps to achieve your goal:

1. Disable all your foreign key constraints with this: EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
2. Then perform all the delete you want
3. Enable all your foreign key constraints with this: exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

You may argue that this is a risky cheat but let me explain: You probabily have a pool of tables seen by the users and some system setting tables. These two groups of tables will not be linked together (if not even your recurive query will say to you that is impossible to delete a "user table" if you don't delete a system setting table). So you can trust my logic and truncate all the "user table" with your eyes closed

CodePudding user response：

Why not keeping a periodic backup of your database with the table you want to truncate empty and load this backup everytime you want a fresh installation of your app