October 20, 2015 at 11:23 am
Is there a way to find all the table names that have a foreign key that references FILE_ID_IN([FILE_ID])
Note: The table def below has a foreign key
IF object_id('CODE_MAP_IN', 'U') IS NOT NULL DROP TABLE CODE_MAP_IN
Print 'Creating table CODE_MAP_IN...'
Create table CODE_MAP_IN
(
[CODE_MAP_FR] [varchar] (53),
[CODE_MAP_TO] [varchar] (53),
[FILE_ID] [float] DEFAULT 1000
PRIMARY KEY ([CODE_MAP_FR]),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]
GO
October 20, 2015 at 12:10 pm
Will this get you what you are looking for? :
select fk.name as FKName,
object_name(fk.object_Id) as TableName,
object_name(o.object_id) as REFName
from sys.foreign_keys fk
inner join sys.objects o
on fk.referenced_object_id = o.object_id
Where object_name(o.object_id) = 'FILE_ID_IN'
**Had to update the where clause for the table name you are looking for in the example
October 20, 2015 at 12:29 pm
Yes,
But had to change it slightly...... ( Yours, the first 2 columns had the same values )
select fk.name as FKName,
object_name(fk.parent_object_Id) as TableName,
object_name(o.object_id) as REFName, fk.*
from sys.foreign_keys fk
inner join sys.objects o
on fk.referenced_object_id = o.object_id
Where object_name(o.object_id) = 'FILE_ID_IN'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply