Neste artigo vamos ver como é possível listar todas as chaves primárias e estrangeiras de bancos de dados MySQL e SQL Server.

Vamos começar pelo MySQL. Para listar as chaves primárias do banco de dados podemos fazer uma consulta ao banco de dados "information_schema" e utilizar as tabelas "information_schema.table_constraints" e "information_schema.key_column_usage":

SELECT t.table_name as nome_tabela, k.column_name as coluna

FROM information_schema.table_constraints t

JOIN information_schema.key_column_usage k

USING(constraint_name,table_schema,table_name)

WHERE t.constraint_type='PRIMARY KEY'

Perceba no exemplo acima que filtramos pelo campo "constraint_type" da tabela "table_constraints".

Para retornar as chaves estrangeiras, basta alterar o parâmetro

SELECT i.table_name as nome_tabela, 

i.constraint_name as nome_fk, 

k.referenced_table_name as tabela_referencia, 

k.referenced_column_name as coluna_tabela_referencia 

FROM information_schema.table_constraints i 

LEFT JOIN information_schema.key_column_usage k 

ON i.constraint_name = k.constraint_name 

WHERE i.constraint_type = 'FOREIGN KEY' 

Agora vamos ver como fazer isso no SQL Server. Começando pela lista de chaves primárias:

SELECT schema_name(tab.schema_id) as [nome_schema], 

pk.[name] as pk_nome,

SUBSTRING(column_names, 1, len(column_names)-1) as [colunas],

tab.[name] as nome_tabela

FROM sys.tables tab

INNER JOIN sys.indexes pk

ON tab.object_id = pk.object_id 

AND pk.is_primary_key = 1

CROSS APPLY (select col.[name] + ', '

FROM sys.index_columns ic

INNER JOIN sys.columns col

ON ic.object_id = col.object_id

AND ic.column_id = col.column_id

WHERE ic.object_id = tab.object_id

AND ic.index_id = pk.index_id

ORDER by col.column_id

FOR XML PATH ('') ) D (column_names)

ORDER BY schema_name(tab.schema_id), pk.[name]

Perceba que fizemos vários JOINS utilizando as tabelas "sys.tables", "sys.indexes", "sys.index_columns", e "sys.columns". Além dos JOINS, utilizamos o CROSS APPLY

O APPLY permite que você execute uma função com valor de tabela para cada linha retornada por uma expressão de tabela externa de uma consulta. 

Sua utilização ocorre da seguinte forma, a função com valor de tabela age como a entrada à direita e a expressão de tabela exterior age como a entrada à esquerda. 

O valor de entrada à direita é avaliado para cada linha da entrada à esquerda. As linhas produzidas são combinadas para fazer o retorno. A lista de colunas produzidas é o conjunto de colunas na entrada à esquerda, seguido pela lista de colunas retornadas pela entrada à direita.

Existem duas formas de utilizar o APPLY. Ele pode ser CROSS APPLY ou OUTER APPLY. O CROSS APPLY seria semelhante a um INNER JOIN, já o OUTER APPLY sera como o LEFT JOIN.

Adicionando o FOR XML no final de uma consulta permite que a saída dos resultados seja como elementos XML, com o nome do elemento contido dentro do argumento do PATH

Nos resultados, para convert o ID do schema da tabela "sys.tables" no nome foi necessário utilizar a função SCHEMA NAME.

Para obter as chaves estrangeiras o processo é um pouco mais trabalhoso.

SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name as tabela_estrangeira,

'>-' as rel,

schema_name(pk_tab.schema_id) + '.' + pk_tab.name as tabela_primaria,

SUBSTRING(column_names, 1, len(column_names)-1) as [fk_columns],

fk.name as fk_constraint_name

FROM sys.foreign_keys fk

INNER JOIN sys.tables fk_tab

ON fk_tab.object_id = fk.parent_object_id

INNER JOIN sys.tables pk_tab

ON pk_tab.object_id = fk.referenced_object_id

CROSS APPLY (select col.[name] + ', '

FROM sys.foreign_key_columns fk_c

INNER JOIN sys.columns col

ON fk_c.parent_object_id = col.object_id

AND fk_c.parent_column_id = col.column_id

WHERE fk_c.parent_object_id = fk_tab.object_id

AND fk_c.constraint_object_id = fk.object_id

ORDER BY col.column_id

FOR XML PATH ('') ) D (column_names)

ORDER BY schema_name(fk_tab.schema_id) + '.' + fk_tab.name,

schema_name(pk_tab.schema_id) + '.' + pk_tab.name