Show Table Parents

,

Need to insert a row into tableX?  Well, you'll first need to enter rows into required foreign key column tables and their parents too.

This procedure, ShowTableParents, will display in inverted tree order all the multiple optional and required foreign key/parent table rows.  You must customize this to exclude all denormalized column foreign keys to avoid infinite loops.

I could have written this more simply as a recursive function but it would run slower.  This is written using a table variable as a stack to simulate recursion.

use AdventureWorks2008

-- try this:

exec dbo.ShowTableParents 'Sales.Store',0,'|--';

-- Here is the output; notice the third line Sales.SalesPerson is not required, thus, nor any table below its level:

Sales.Store

|--Person.BusinessEntity!

|--Sales.SalesPerson

|--|--HumanResources.Employee!

|--|--|--Person.Person!

|--|--|--|--Person.BusinessEntity!

|--|--Sales.SalesTerritory

|--|--|--Person.CountryRegion!

IF  EXISTS 
(
	SELECT *
	FROM sys.procedures
	WHERE name = 'ShowTableParents'
	AND type = N'P'
	AND schema_id = SCHEMA_ID('dbo')
)
DROP PROCEDURE dbo.ShowTableParents
GO

CREATE PROCEDURE dbo.ShowTableParents
	@TableName varchar(255), -- schema.tablename
	@RequiredParents bit = 1, -- 0 for all foreign keys
	@Spacer varchar(10) = '	' -- tab character
as

SET NOCOUNT ON;

-- get all foreign keys
DECLARE @x TABLE (ChildName varchar(255) NOT NULL, ParentName varchar(255) NOT NULL, is_nullable bit NOT NULL);

INSERT INTO @x
SELECT DISTINCT
	(SELECT SCHEMA_NAME(t.schema_id) FROM sys.tables t WHERE t.object_id = fk.parent_object_id)
		+'.'+OBJECT_NAME(fk.parent_object_id) AS Child,
	(SELECT SCHEMA_NAME(t.schema_id) FROM sys.tables t WHERE t.object_id = fk.referenced_object_id)
		+'.'+OBJECT_NAME(fk.referenced_object_id) AS Parent,
	c.is_nullable
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
;

IF @RequiredParents = 1
DELETE FROM @x WHERE is_nullable = 1
;

-- no circular ref in db -- customize this for your denormalized columns or you will get an endless loop.
-- ParentX and ParentY both have foreign keys pointing to each other.
DELETE FROM @x
WHERE ChildName = 'dbo.ParentX'
AND ParentName = 'dbo.ParentY'
;

-- self references cause infinite loops so delete them all
DELETE FROM @x
WHERE ChildName = ParentName
;

--SELECT * FROM @x; --testing

-- use a stack to walk the tree
DECLARE @stack TABLE (StackLevel int NOT NULL, TableName varchar(128) NOT NULL);

DECLARE
	@ChildTableName varchar(255),
	@ParentTableName varchar(255),
	@StackLevel int,
	@req varchar(1)
;

SET @StackLevel = 0;
SET @ChildTableName = @TableName;
INSERT INTO @stack VALUES (@StackLevel, @ChildTableName);

PRINT @ChildTableName;

WHILE @StackLevel >= 0
BEGIN
	SET @StackLevel = (SELECT MAX(StackLevel) FROM @stack);

	-- always first try to add another level to stack
	SET @ParentTableName =
	(
	SELECT MIN(x.ParentName)
	FROM @x x
	JOIN @stack s ON x.ChildName = s.TableName
	WHERE s.StackLevel = @StackLevel
	);

	-- parent added
	IF @ParentTableName IS NOT NULL
	BEGIN
		SET @StackLevel += 1;

		INSERT INTO @stack VALUES (@StackLevel, @ParentTableName);

		SET @req = 
		(
		SELECT CASE WHEN is_nullable=1 THEN '' ELSE '!' END
		FROM @x x
		WHERE x.ChildName = (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel - 1)
		AND x.ParentName = (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel)
		);
 
		PRINT REPLICATE(@Spacer,@StackLevel)+@ParentTableName+@req;

		CONTINUE;
	END

	-- now try to get next sibling of highest parent
	SET @ParentTableName =
	(
	SELECT MIN(x.ParentName)
	FROM @x x
	WHERE x.ChildName = (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel - 1)
	AND x.ParentName > (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel)
	);

	IF @ParentTableName IS NOT NULL
	BEGIN

		UPDATE @stack SET TableName = @ParentTableName WHERE StackLevel = @StackLevel;

		SET @req = 
		(
		SELECT CASE WHEN is_nullable=1 THEN '' ELSE '!' END
		FROM @x x
		WHERE x.ChildName = (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel - 1)
		AND x.ParentName = (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel)
		);
 
		PRINT REPLICATE(@Spacer,@StackLevel)+@ParentTableName+@req;

		CONTINUE;
	END

	backtrack:
	-- backtrack and get next sibling of previous parent
	DELETE FROM @stack WHERE StackLevel = @StackLevel;
	SET @StackLevel -= 1;
	-- now try to get next sibling of highest parent
	SET @ParentTableName =
	(
	SELECT MIN(x.ParentName)
	FROM @x x
	WHERE x.ChildName = (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel - 1)
	AND x.ParentName > (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel)
	);

	IF @ParentTableName IS NOT NULL
	BEGIN
		UPDATE @stack SET TableName = @ParentTableName WHERE StackLevel = @StackLevel;

		SET @req = 
		(
		SELECT MAX(CASE WHEN is_nullable=1 THEN '' ELSE '!' END)
		FROM @x x
		WHERE x.ChildName = (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel - 1)
		AND x.ParentName = (SELECT s.TableName FROM @stack s WHERE s.StackLevel = @StackLevel)
		);
 
		PRINT REPLICATE(@Spacer,@StackLevel)+@ParentTableName+@req;

		CONTINUE;
	END

	IF @StackLevel > 0 GOTO backtrack;
	BREAK;
END

RETURN 0;
GO

Rate

2 (1)

Share

Share

Rate

2 (1)