这是我的设置:我在我的Windows PC上使用SQL SERVER,我有一台在Windows Server上安装了SQL SERVER的服务器...
这里的要点是,我需要开发一个过程来在任何给定时间截断数据库中的所有表……要在我的大型数据库(仍在开发和增长中)上做到这一点,我需要一个可以找到所有外键依赖层次 struct 的查询,这样我就可以截断查询给出的列表中的所有表……我发现recursive查询是在线的,但重点是the query execute on the server but not on localhost...查询需要5(最大)的递归级别,但即使我在查询(option (maxrecursion 100))中指定它,它也不会在本地执行……
以下是查询:
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);