SQL how do you query for tables that refer to a specific foreign key value?

Question

I have table A with a primary key on column ID and tables B,C,D... that have 1 or more columns with foreign key relationships to A.ID.

How do I write a query that shows me all tables that contain a specific value (eg 17) of the primary key?

I would like to have generic sql code that can take a table name and primary key value and display all tables that reference that specific value via a foreign key.

The result should be a list of table names.

I am using MS SQL 2012.

Answer 1

You want to look at sys.foreignkeys. I would start from http://blog.sqlauthority.com/2009/02/26/sql-server-2008-find-relationship-of-foreign-key-and-primary-key-using-t-sql-find-tables-with-foreign-key-constraint-in-database/

to give something like

declare @value nvarchar(20) = '1'

SELECT 
    'select * from '

    + QUOTENAME( SCHEMA_NAME(f.SCHEMA_ID))
    + '.'
    + quotename( OBJECT_NAME(f.parent_object_id) )
    + ' where '
    + COL_NAME(fc.parent_object_id,fc.parent_column_id) 
    + ' = '
    + @value

FROM sys.foreign_keys AS f
 INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
 INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Answer 2

Not an ideal one, but should return what is needed (list of tables):

declare @tableName sysname, @value sql_variant

set @tableName = 'A'
set @value = 17

declare @sql nvarchar(max)

create table #Value (Value sql_variant)
insert into #Value values (@value)

create table #Tables (Name sysname, [Column] sysname)
create index IX_Tables_Name on #Tables (Name)

set @sql = 'declare @value sql_variant
select @value = Value from #Value

'
set @sql = @sql + replace((
select
    'insert into #Tables (Name, [Column])
select ''' + quotename(S.name) + '.' + quotename(T.name) + ''', ''' + quotename(FC.name) + '''
where exists (select 1 from ' + quotename(S.name) + '.' + quotename(T.name) + ' where ' + quotename(FC.name) + ' = @value)
'
from
    sys.columns C
    join sys.foreign_key_columns FKC on FKC.referenced_column_id = C.column_id and FKC.referenced_object_id = C.object_id
    join sys.columns FC on FC.object_id = FKC.parent_object_id and FC.column_id = FKC.parent_column_id
    join sys.tables T on T.object_id = FKC.parent_object_id
    join sys.schemas S on S.schema_id = T.schema_id
where
    C.object_id = object_id(@tableName)
    and C.name = 'ID'
order by S.name, T.name
for xml path('')), '
', CHAR(13))

--print @sql
exec(@sql)

select distinct Name
from #Tables
order by Name

drop table #Value
drop table #Tables

Answer 3

You could achive that by writing some SQL. I post an example but it is just a mockup showing the way you could do it.

CREATE TABLE tempTable
(
     TABLE_NAME varchar(255)
);

CREATE UNIQUE CLUSTERED INDEX Idx_tempTable ON tempTable(TABLE_NAME);

DECLARE @var2 nvarchar(max)

INSERT INTO tempTable 
       SELECT DISTINCT 
              TABLE_NAME 
       FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE COLUMN_NAME LIKE '%COLUMN_NAME%'

/*FOREACH result of the tempTable you could find if the COLUMN_NAME of the result(table) has the value you want*/

SET @var2 = 'SELECT TABLE_NAME FROM ' + tempTableResult + ' WHERE COLUMN_NAME=VALUE'

exec(@var2)

DROP TABLE tempTable

Answer 4

The query will return a list of table names and append those names with the data (if used to find), or a "(no date)" if child data are held as daily instances.

Also, apologies up front for the use of a cursor. I tend to use them only for special cases such as this one (i.e. finding the few odd records that may exist across 100's of tables).

In my case, a table references just under 400 tables (all of which are generated automatically as part of a "learning" system), and depending on the type of entry saved, data may or may not written into these tables. A further twist is some of these data are also by-date, so the query must also check for the existence of a date column in each table with the foreign key (fortunately, in these instances the column will always be named "dt").

From the nearly 400 tables listed as referencing the "asset" table. Only a dozen tables actually held data for the particular entry I was investigating. All of the tables held the data as daily instances/detail.

The referenced table's name is "asset" and the Dynamic SQL includes a sub query (convert a human readable name to a primary key, used as a FK value).

The cursor query is from Gishu at How can I list all foreign keys referencing a given table in SQL Server?

DECLARE @TableName varchar(255) DECLARE @FKeyColumn varchar(255) DECLARE @rowcount int DECLARE @sqlCMD NVARCHAR(500) DECLARE @dt NVARCHAR(10) = '2008-08-25' DECLARE @SymbolName NVARCHAR(9) = 'thingImLookingFor' DECLARE @byDate varchar(255) DECLARE TableCursor CURSOR FOR select t.name as TableWithForeignKey, c.name as ForeignKeyColumn from sys.foreign_key_columns as fk inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id where fk.referenced_object_id = (select object_id from sys.tables where name = 'asset') OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName, @FKeyColumn WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlCMD = 'SELECT @rowcount=count(*) FROM ' + @TableName + ' WHERE ' + @FKeyColumn + '=(SELECT asset_id FROM asset WHERE primary_symbol=''' + @SymbolName + ''')' SET @byDate = ' (no date)' IF EXISTS(SELECT 1 FROM sys.columns WHERE sys.columns.name = N'dt' AND sys.columns.object_id = Object_ID(@TableName)) BEGIN SET @sqlCMD = @sqlCMD + ' AND dt=''' + @dt + '''' SET @byDate = ' (' + @dt + ')' END EXEC sp_executesql @sqlCMD, N'@rowcount int output', @rowcount output IF(@rowcount=1) PRINT(@TableName + @byDate) FETCH NEXT FROM TableCursor INTO @TableName, @FKeyColumn END CLOSE TableCursor; DEALLOCATE TableCursor;