Show Table Parents

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