I was granted access to a SQL Server database and can connect and query the data. However, I want to check for any explicit foreign keys and other relationships in the data model, but don't see any. I'm not sure if this is because there just aren't any defined, or a permissions issue, or an issue related to DataGrip.

What's odd is that in PowerBI, I can see a few relationships, but that might have been auto-generated when I connected to the database.

How do I know if my account was not granted permissions to view table relationships? Here are the things that I can do right now:

• Query the data
• Extract the DDL statement for any table within DataGrip.

Not sure what else to try.

CodePudding user response：

You can try the following query.

SQL

SELECT OBJECT_NAME(f.parent_object_id) AS table_name  
    , f.name AS foreign_key_name  
    ,COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS constraint_column_name  
    ,OBJECT_NAME (f.referenced_object_id) AS referenced_object  
    ,COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS referenced_column_name  
   , [column_type] = t.name
    --,f.is_disabled, f.is_not_trusted
    --,f.delete_referential_action_desc  
    --,f.update_referential_action_desc  
FROM sys.foreign_keys AS f
   INNER JOIN sys.foreign_key_columns AS fkc   
      ON f.object_id = fkc.constraint_object_id
   LEFT JOIN sys.all_columns ac on fkc.referenced_object_id = ac.object_id 
      and fkc.referenced_column_id = ac.column_id
   INNER JOIN sys.types t on ac.system_type_id = t.system_type_id
--WHERE f.parent_object_id = OBJECT_ID('dbo.PropertySegment')
ORDER BY table_name; 

CodePudding user response：

If you use the same account and can see the table relationships in the Power BI, but not in the Database Explorer of DataGrip, then it's unlikely to be a permission-related issue. First, try to refresh the schema where these relationship objects are located. If the objects don't appear under the table when you expand the tree, please open a ticket.