How to find a lookup table from a table column?

Question

Hello,
I am new to the SQL Server Management Studio 18 , and I would like to seek your helps, and expertise on how to find a lookup table from a table columns.

For example I have a CurrencyRate table. In the currencyRate, I have a column name CurrencyKey. It is refer to as a FK to another table named CurrencyDim which has the currencyKey as a PK.

Supposely that I don't have any of the above information described teh relationship of those two above tables; I oonly have the CurrencyRate table. Is there a way for me to use either

1. The SQL Server Management Studio tool if there is any tool to find out the relationship of other table from the CurrencyRate table?
2. Is there q sql query that I can use to find out those relationship?

The reason is that I want to find out the relationship because I plan to learn to create a SSIS package which would need a lookup transformat to match the cotent of my text file data to the target table columns.

Thanks again for all of the helps.
Du

Answer 1

Thank you so much for the help.
The following query give the referenced table. May I ask if I would like to get additional information such as

1. The referenced table,
2. It s primary key , and data type

Then how do we approach it ? Is there a way to modify the following query in order to obtain the referenced table, its primary keys , and data types.

  select OBJECT_NAME(referenced_object_id)
 FROM sys.foreign_key_columns
 WHERE OBJECT_NAME(parent_object_id) = 'CurrencyRate'
 GO

Many Thanks for the help,
Du

Answer 2

Hi,

For example I have a CurrencyRate table. In the currencyRate, I have a column name CurrencyKey. It is refer to as a FK to another table named CurrencyDim which has the currencyKey as a PK.

Please avoid such stories in the future and instead simply provide DDL+DML (queries to create the tables and insert some demo rows)

According to your description you have something like bellow sample

------------------------------- DDL  

CREATE DATABASE ForumTest  
GO  
  
use ForumTest  
GO  
  
CREATE TABLE CurrencyDim (  
    currencyKey int NOT NULL PRIMARY KEY,  
 txt varchar(10)  
);  
  
CREATE TABLE CurrencyRate (  
 id int primary key,  
 CurrencyKey int,  
 FOREIGN KEY (CurrencyKey) REFERENCES CurrencyDim(currencyKey)  
)  
GO  

how to find a lookup table from a table columns... I oonly have the CurrencyRate table. Is there a way for me to use either... Is there q sql query that I can use to find out those relationship?

You can use the sys.foreign_key_columns table in order to get the lookup table if you have the main table name

select OBJECT_NAME(referenced_object_id)  
FROM sys.foreign_key_columns  
WHERE OBJECT_NAME(parent_object_id) = 'CurrencyRate'  
GO  

how to find a lookup table from a table columns...The SQL Server Management Studio tool if there is any tool to find out the relationship of other table from the CurrencyRate table?

Yes, there is a tool you can use for this information but You should NOT count on such solution in my opinion and you should not count on graphycal client tools as such

The tool is the SSMS DATABASE Diagram

You can create a diagram to the database

Next add the main table to the diagram

Next in the diagram right click on the table name and select "add related table"

This will add the lookup table to the diagram

Answer 3

Hi,@minh tran

Welcome to Microsoft T-SQL Q&A Forum!

The foreign key table used is based on the DDL provided by Pituach . You can make an inner join between sys.foreign_keys and sys.foreign_key_columns to easily get what you want , like this:

  SELECT     
    f.name AS foreign_key_name    
   ,OBJECT_NAME(f.parent_object_id) AS table_name    
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name    
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object    
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_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 fc     
   ON f.object_id = fc.constraint_object_id     
WHERE f.parent_object_id = OBJECT_ID('CurrencyRate');    

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi,

Many Thanks for the help,

You are most welcome

in order to obtain the referenced table, its primary keys , and data types.

In order to get the information about the column you can use the system tables: sys.all_columns and sys.types

select   
	[Main Table] = OBJECT_NAME(fkc.parent_object_id),  
	[lookup table] = OBJECT_NAME(fkc.referenced_object_id),   
	[lookup column name] = c.name,  
	[lookup column type name] = t.name  
FROM sys.foreign_key_columns fkc  
LEFT JOIN sys.all_columns c on fkc.referenced_object_id = c.object_id and fkc.referenced_column_id = c.column_id  
INNER JOIN sys.types t on c.system_type_id = t.system_type_id  
WHERE OBJECT_NAME(parent_object_id) = 'CurrencyRate'  
GO  

Same way using the same table you can get more information about the columns.

If you need more information about the tables then you can also use sys.tables

Answer 5

supposely that I don't have any of the above information described teh relationship of those two above tables

Then you can only guess, by names or any further hint.