SQL Relationships, foreign keys-CodePudding

I want to ask if there is a way to see the relationships, foreign keys in a specific table using SQL query. So far I have tried different stuff on the internet but with no success.

I have tried

EXEC sp_fkeys 'TableName'

which seems like the shortest way, but it's not working. I'm using myPhpAdmin.

CodePudding user response：

I have found a solution for MariaDB server

select fks.table_name as foreign_table,
       '->' as rel,
       fks.referenced_table_name
              as primary_table,
       fks.constraint_name,
       group_concat(kcu.column_name
            order by position_in_unique_constraint separator ', ') 
             as fk_columns
from information_schema.referential_constraints fks
join information_schema.key_column_usage kcu
     on fks.constraint_schema = kcu.table_schema
     and fks.table_name = kcu.table_name
     and fks.constraint_name = kcu.constraint_name
    where fks.constraint_schema = 'HERE YOU WRITE THE NAME OF YOU DATA BASE'
group by fks.constraint_schema,
         fks.table_name,
         fks.unique_constraint_schema,
         fks.referenced_table_name,
         fks.constraint_name
order by fks.constraint_schema,
         fks.table_name;

the result is a table with all table names and their connections

CodePudding user response：

There query shows foreign key info:

select o2.name as ParentTableName, o.name as ReferencedTableName, fk.name as foreignKey_name, *
from sys.foreign_keys fk
inner join sys.objects o on o.object_id = fk.parent_object_id
inner join sys.objects o2 on o.object_id = fk.referenced_object_id
where o.name = 'table_name' or o2.name = 'table_name'