How to find a lookup table from a table column?

Question

How to find a lookup table from a table column?

minh tran 216

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

The SQL Server Management Studio tool if there is any tool to find out the relationship of other table from the CurrencyRate table?
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

Bert Zhou-msft 3,436 Reputation points

2022-05-27T01:54:58.897+00:00

Hi ,@minh tran

Have you tested the answers below, are there any updates to this question? Please contact us in your free time and we will continue to help you make progress.

Bert Zhou

10 answers

Your answer

Bert Zhou-msft 3,436 Reputation points

2022-05-27T01:54:58.897+00:00

Hi ,@minh tran

Have you tested the answers below, are there any updates to this question? Please contact us in your free time and we will continue to help you make progress.

Bert Zhou

Answer 1

minh tran 216

Thank you so much for all of the help.
I have one question regarding learning SSIS and I would like to seek your advice.

Since I am a beginner using Visual Studio 2015 to learn the SSIS process package creating for datawarehouse,
could you help point my to book or learning resources that I can learn on my own time?

Thanks again for all of the wonderful helps.

Du

Bert Zhou-msft 3,436 Reputation points

2022-05-24T01:21:21.817+00:00

Hi,@minh tran

If you want to learn SSIS, follow this link and if the above answer helped you, please endorse it, it will help others who have the same problem as you.

Bert Zhou

Answer 2

Good morning,
I am so sorry for asking you again on the clarification of the script finding foreign key of the FactCurrencyRate table in the AdventureWork2019 database.

Accoring to the diagram below, the FactCurrencyRate table has two foereign keys refer to the DimCurrency table and the DimDate table. So when I ran the script below , it should give me those two referenced tables and their primaky keys

use AdventureWorksDW2019  
  
 select OBJECT_NAME(referenced_object_id)  
 FROM sys.foreign_key_columns  
 WHERE OBJECT_NAME(parent_object_id) = 'dbo.FACTCurrencyRate'  
 GO

However, it gave me none of the referenced tables and their primary keys when I ran the script above to find depencies of the FactCurrencyRate table.

Could you please help to suggest what I need to do in order to find the dependencies of the FactCurrency table because I need to use it in the Lookup Transformation for my learning of SSIS ?

Thank you very much for the help.

Du

Yitzhak Khabinsky 26,586 Reputation points

2022-05-27T18:28:44.867+00:00

Just try the @Bert Zhou-msft solution. It is working properly.

Answer 3

minh tran 216

Thanks but it did not let me to see his solution.

I tried to click on the link, and it gave me Bert Zhou-msft's profile is private.

Is there a way for me to view his note?

Thanks,
Du

Yitzhak Khabinsky 26,586 Reputation points

2022-05-27T19:22:31.57+00:00

You don't need any link.

Just check @Bert Zhou-msft solution in this thread, and copy its T-SQL statement to your SSMS.

Answer 4

minh tran 216

May I ask how can I see his solution ?
It did not let me to see his solution. It said his account is private.
Thanks,
Du

Yitzhak Khabinsky 26,586

@minh tran ,

Here we go.

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');

Answer 5

minh tran 216

Great. Thabk you so much

Bert Zhou-msft 3,436 Reputation points

2022-05-30T02:21:30.133+00:00

Hi，@minh tran
If you endorse the answer, please flip it up and endorse it , it will help others who have the same problem as you.

Best regards,
Bert Zhou

Share via

How to find a lookup table from a table column?

10 answers

Your answer