Bruno Perroni
DBA SQL Server com mais de 6 anos de experiência!

Relacionamento entre Tabelas

Eae pessoal, tudo certo ?

Nesse post estou trazendo 3 scripts bem legais para localizar facilmente o relacionamento entre tabelas.

Sendo que no ultimo você pode visualizar o relacionamento de uma tabela especifica, passando o seu nome.

SELECT fk.NAME AS [Chave Estrangeira (Foreign Key)]
	,tp.NAME   AS [Tabela Pai]
	,cp.NAME   AS [Campo Relaciona Tabela Pai]
	,tr.NAME   AS [Tabela Filho]
	,cr.NAME   AS [Campo Relaciona Tabela Filho]
FROM sys.foreign_keys fk
INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id
	AND fkc.parent_object_id = cp.object_id
INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id
	AND fkc.referenced_object_id = cr.object_id
ORDER BY tp.NAME
	,cp.column_id
SELECT object_name(rkeyid) [Tabela Pai]
	,object_name(fkeyid)   [Tabela Filho]
	,object_name(constid)  [Nome Chave Estrangeira]
	,c1.NAME			   [Chave Estrangeira Tabela Pai (Foreign Key)]
	,c2.NAME		       [Chave Estrangeira Tabela Filho (Foreign Key)]
FROM sys.sysforeignkeys s
INNER JOIN sys.syscolumns c1 ON (
		s.fkeyid = c1.id
		AND s.fkey = c1.colid
		)
INNER JOIN syscolumns c2 ON (
		s.rkeyid = c2.id
		AND s.rkey = c2.colid
		)
ORDER BY [Tabela Pai]
	,[Tabela Filho]
DECLARE @NomeTabela AS VARCHAR(250) = '<schema>.<tabela>';

WITH cte
AS (
	SELECT cast(OBJECT_NAME(fkc.parent_object_id) AS VARCHAR(MAX)) AS [Relacionamento Tabelas]
		,OBJECT_NAME(fkc.parent_object_id) AS [Tabela Dependente]
		,fkc.parent_object_id AS childID
		,1 AS ReLevel
	FROM sys.foreign_key_columns fkc
	WHERE fkc.referenced_object_id = OBJECT_ID(@NomeTabela)
	
	UNION ALL
	
	SELECT cast(c.[Relacionamento Tabelas] + '>>>>' + OBJECT_NAME(fkc.parent_object_id) AS VARCHAR(MAX)) AS [Relacionamento Tabelas]
		,OBJECT_NAME(fkc.parent_object_id) AS [Tabela Dependente]
		,fkc.parent_object_id AS childID
		,c.ReLevel + 1
	FROM sys.foreign_key_columns fkc
	INNER JOIN cte c ON fkc.referenced_object_id = c.Childid
		AND fkc.parent_object_id <> c.childid
	)
SELECT [Relacionamento Tabelas]
	,[Tabela Dependente]
FROM cte