0

I feel like this is a really easy thing to find yet when I look it up I seem to not find exactly what I'm looking for, so I don't know if I'm just not asking it right when googling it. One of the columns on my table's name starts with FK, so clearly it's a foreign key to another table. How do I find this table?

FrostyStraw
  • 1,628
  • 3
  • 25
  • 34
  • This answer may help you: https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server – Chad Levy Jun 26 '17 at 15:46
  • 2
    @FrostyStraw, are you certain there is PK/FK relationship or are you just going by the assumption that because the name begins with FK, the relationship exist? – Rushikumar Jun 26 '17 at 15:49
  • call this in MSMS: EXEC sp_fkeys 'TableName' – GuidoG Jun 26 '17 at 15:50
  • @Rushikumar im assuming, although idk why it would start with FK and there NOT be a relationship? - is there any reason that would ever be the case? – FrostyStraw Jun 26 '17 at 15:53
  • @ChadLevy that seems to do the opposite. It seems to give me tables that point to the table I'm working on. I want the tables my table points to. – FrostyStraw Jun 26 '17 at 15:54
  • 3
    I have seen systems where RI is all handled at the application and the database columns are named like that to indicate they are foreign keys. But in reality there were no keys at all. Needless to say that was not a fun system to work with. You should NEVER assume that something is a foreign key just because it has a name that indicates it should be. – Sean Lange Jun 26 '17 at 15:56
  • @SeanLange ok. but..if i do MAKE that assumption, how can i find out the related table? – FrostyStraw Jun 26 '17 at 16:05
  • @GuidoG posted one solution to this already. You can also look at sys.foreign_keys. If there is no actual key then you are going to have to manually discover the "keys". – Sean Lange Jun 26 '17 at 16:17
  • @SeanLange Yes offcourse there are more than one way to find this info. A small search on google should have helped the OP imho – GuidoG Jun 27 '17 at 06:39
  • @GuidoG I was suggesting you had already posted one method of finding this info. Totally agree that a short "binoogle" session would have yielded the answer for them. – Sean Lange Jun 27 '17 at 13:13
  • @SeanLange Oh yes now I understand. Thats what I get for reading comments to fast... – GuidoG Jun 27 '17 at 13:52

2 Answers2

0

Try the following- all it does is list ALL the FK relationships (and more) within your database. Locate your desired table(s) and go from there.

SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
        SELECT
            i1.TABLE_NAME,
            i2.COLUMN_NAME
        FROM
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE
            i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
       ) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
HuntingtonCream17
  • 33
  • 6
0

One way to identify the foreign key table is to script your foreign key and look at the REFERENCES line. Here is an example:

USE [TirePricing]
GO
ALTER TABLE [dbo].[tblTireLines]  WITH CHECK ADD  CONSTRAINT [FK_tblTireLines_tblManufacturerBrands] FOREIGN KEY([ManufacturerBrandID])
REFERENCES [dbo].[tblManufacturerBrands] ([ManufacturerBrandID])
GO
ALTER TABLE [dbo].[tblTireLines] CHECK CONSTRAINT [FK_tblTireLines_tblManufacturerBrands]
GO

To script the key; find your table in the SQL Server Management Studio Object Explorer tree. Expand it and then your keys folder to find the key in question. Then, right-click on the key and use the Script Key As... command from the context menu.

The REFERENCES section will identify the table and column of the foreign key.

bsivel
  • 2,821
  • 5
  • 25
  • 32