question

minhtran-6417 avatar image
0 Votes"
minhtran-6417 asked BertZhoumsft-7490 commented

How to find a lookup table from a table column?

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




sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi ,@minhtran-6417

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

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

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

204294-image.png

Next add the main table to the diagram

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

204314-image.png


This will add the lookup table to the diagram


image.png (12.7 KiB)
image.png (31.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

minhtran-6417 avatar image
1 Vote"
minhtran-6417 answered

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered

Hi,@minhtran-6417

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

204492-image.png


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.




image.png (7.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered

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

204522-image.png

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


image.png (9.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

minhtran-6417 avatar image
0 Votes"
minhtran-6417 answered BertZhoumsft-7490 edited

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@minhtran-6417

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

0 Votes 0 ·
minhtran-6417 avatar image
0 Votes"
minhtran-6417 answered YitzhakKhabinsky-0887 commented

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.

206304-capture.png

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



capture.png (96.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Just try the @BertZhoumsft-7490 solution. It is working properly.

0 Votes 0 ·
minhtran-6417 avatar image
0 Votes"
minhtran-6417 answered YitzhakKhabinsky-0887 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You don't need any link.

Just check @BertZhoumsft-7490 solution in this thread, and copy its T-SQL statement to your SSMS.

0 Votes 0 ·
minhtran-6417 avatar image
0 Votes"
minhtran-6417 answered YitzhakKhabinsky-0887 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@minhtran-6417,

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');  
0 Votes 0 ·
minhtran-6417 avatar image
0 Votes"
minhtran-6417 answered BertZhoumsft-7490 commented

Great. Thabk you so much

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@minhtran-6417
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


0 Votes 0 ·