Stored Procedure to Get all Parent and Child tables within all

create table TabRelation
(
ParentTable varchar(max),
ChildTable varchar(max)
)




-- Get Child tables

alter PROCEDURE TabRelations2
@ParentTable varchar(max)
As
begin
set nocount on

--insert into Tables
--select @cTableName

declare @cTab varchar(max)

/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR
SELECT distinct OBJECT_NAME (f.parent_object_id)
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id
where OBJECT_NAME (f.parent_object_id) <> OBJECT_NAME (f.referenced_object_id)
and OBJECT_NAME (f.referenced_object_id) = @ParentTable

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab
WHILE @@FETCH_STATUS = 0
BEGIN
exec TabRelations2 @cTab
FETCH NEXT FROM cFKey INTO @cTab
END
Close cFKey
DeAllocate cFKey

INSERT INTO TabRelation
SELECT distinct @ParentTable, OBJECT_NAME (f.parent_object_id)
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id
where OBJECT_NAME (f.parent_object_id) <> OBJECT_NAME (f.referenced_object_id)
and OBJECT_NAME (f.referenced_object_id) = @ParentTable

end
go



--Get Parent tables

CREATE PROCEDURE TabRelations1
@ChildTable varchar(max)
As
begin
set nocount on

--insert into Tables
--select @cTableName

declare @cTab varchar(max)

/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR
SELECT distinct OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id
where OBJECT_NAME (f.parent_object_id) <> OBJECT_NAME (f.referenced_object_id)
and OBJECT_NAME (f.parent_object_id) = @ChildTable

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab
WHILE @@FETCH_STATUS = 0
BEGIN
exec TabRelations1 @cTab
FETCH NEXT FROM cFKey INTO @cTab
END
Close cFKey
DeAllocate cFKey

INSERT INTO TabRelation
SELECT distinct OBJECT_NAME (f.referenced_object_id) , @ChildTable
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id
where OBJECT_NAME (f.parent_object_id) <> OBJECT_NAME (f.referenced_object_id)
and OBJECT_NAME (f.parent_object_id) = @ChildTable

end
go