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