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
(
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
Comments
Post a Comment