/*============================
NAME: Get foreign key hierarchy of all DB tables
(to determine tables INSERT or DROP order, for example)
DESCRIPTION: This is a short script that returns all table names
in the current database, together with their foreign key (FK)
hierarchy level, and the table(s) that they reference (when
applicable). The value of the FK hierarchy associated with
each table is determined as follows: If a table does not
have a FK constraint (i.e., it does not reference any other
tables via a FK, or in other words - the table is not a foreign
table in any FK relationship), then it is of level 0 in the
hierarchy. If the table references one or more tables,
which do not reference any other tables, then the current
table is of level 1, and so on. The tables referenced by
each FK (i.e., primary tables) are returned by the script
as well, for each FK relationship found. Moreover, if a table
references itself (and no other tables), then it is
considered as a level 0 table.
The script is useful when one wishes to INSERT data into
several tables, or DROP tables, and needs to determine the
table order to follow - tables of hierarchy 0 must be
inserted into first, then those of hierarchy 1, and so on.
Similarly, tables with the highest hierarchy should be dropped
first, and those with hierarchy 0 should be dropped last.
To return the table FK hierarchy info, the script uses the
following algorithm: First, get all DB tables that do not
have any FK constraints. Then get all tables that have a
FK that only reference one or more of the tables that don't
have any FKs. Then, get the tables that have FKs mapped
to the already collected tables, and so on. The entire
algorithm is run in a simple WHILE loop.
USER PARAMETERS: NA
RESULTSET: TableName, HierarchyLevel, FKName, FKReference (the primary
table in the FK relationship, where applicable)
RESULTSET SORT: NA
USING TABLES/VIEWS: INFORMATION_SCHEMA.TABLES
sysreferences
REVISIONS
DATE DEVELOPER DESCRIPTION OF REVISION VERSION
====== =========== ========================== ===========
05/05/2005 Omri Bahat Initial release 1.00
==========
Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
This code may be used at no charge as long as this copyright notice is not removed.
===========
-- Get FK hierarchy of all DB tables
SET NOCOUNT ON
DECLARE @i INT
DECLARE @Cnt INT
-- The variable @i is the hierarchy level.
-- The variable @Cnt hold the number of tables returned in the
-- last run of the loop, which tells when the loop should exist.
SET @i = 0
SET @Cnt = 1
IF OBJECT_ID('tempdb..#
DROP TABLE #tblFKTableOrder
CREATE TABLE #tblFKTableOrder (
TableName NVARCHAR(128),
HierarchyLevel INT,
FKName NVARCHAR(128),
FKReference NVARCHAR(128))
-- First, grab all the tables that don't have any FK constraints, as hierarchy level 0.
INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT TABLE_NAME, @i, N'', N''
FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK)
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE
-- Second, get all tables that only have self-referencing (and no other) FKs.
-- In the query below - RS1 contains all table names that references themselves
-- (and possible other tables), and RS2 contains all tables that reference other tables.
-- The desired tables are all those in RS1 that are not in RS2.
INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT OBJECT_NAME(RS1.fkeyid), @i, OBJECT_NAME(RS1.constid),OBJECT_NAME(RS1.rkeyid)
FROM (SELECT fkeyid, constid, rkeyid
FROM sysreferences WITH (NOLOCK)
WHERE rkeyid = fkeyid ) RS1
LEFT OUTER JOIN
(SELECT DISTINCT fkeyid
FROM sysreferences WITH (NOLOCK)
WHERE fkeyid <> rkeyid ) RS2
ON RS1.fkeyid = RS2.fkeyid
WHERE RS2.fkeyid IS NULL
-- Now, drill down in the FK hierarchy. Get all tables
-- that have a FK that references one or more tables in #tblFKTableOrder,
-- yet only references tables that are in #tblFKTableOrder(!), and that have not yet
-- been recorded in #tblFKTableOrder. Tables that reference themselves, as well
-- as tables in #tblFKTableOrder, are considered as well.
-- This is done in a loop, and the loop terminates when we reach the lowest level
-- in the hierarchy (i.e., when no more tables meet the listed condition).
WHILE @Cnt > 0
BEGIN
-- Analyze the next level in the hierarchy.
SET @i = @i + 1
-- Get all tables that reference tables that are recorded
-- in #tblFKTableOrder (can also reference themselves),
-- and do not references tables that
-- were not yet recorded.
-- This is done by as follows:
-- RS1 conatains the tables that have FK constraints
-- that reference tables in #tblFKTableOrder (and possibly have
-- a self-reference). RS2 contains all tables that reference tables
-- that are not yet in #tblFKTableOrder (excluding self-refences).
-- We write into #tblFKTableOrder the tables in RS1, which are
-- not in RS2.
INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName,FKReference)
SELECT OBJECT_NAME(a.fkeyid), @i, OBJECT_NAME(a.constid), OBJECT_NAME(a.rkeyid)
FROM sysreferences a
INNER JOIN
(SELECT DISTINCT z.fkeyid
FROM sysreferences z WITH (NOLOCK)
INNER JOIN #tblFKTableOrder y WITH (NOLOCK)
ON OBJECT_NAME(z.rkeyid) = y.TableName
LEFT OUTER JOIN #tblFKTableOrder v WITH (NOLOCK)
ON OBJECT_NAME(z.fkeyid) = v.TableName
WHERE v.TableName IS NULL) RS1
ON a.fkeyid = RS1.fkeyid
LEFT OUTER JOIN
(SELECT DISTINCT x.fkeyid
FROM sysreferences x WITH (NOLOCK)
LEFT OUTER JOIN #tblFKTableOrder w WITH (NOLOCK)
ON OBJECT_NAME(x.rkeyid) = w.TableName
WHERE x.fkeyid <> x.rkeyid
AND w.TableName IS NULL) RS2
ON RS1.fkeyid = RS2.fkeyid
WHERE RS2.fkeyid IS NULL
SET @Cnt = @@ROWCOUNT
END
SET NOCOUNT OFF
SELECT * FROM #tblFKTableOrder
ORDER BY HierarchyLevel ASC, TableName ASC, FKName ASC
GO
No comments:
Post a Comment
Your Comments/Posts are invited...