CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keys
piranha
piranha

Reputation: 141

Display all tables that have a foreign key equal to a value

I'm going to attempt to describe this in the best way that I can. I need to test that a deletion is properly processing through all the tables in a database. The only tables that need to be checked are the ones that have a foreign key to the Users table with Primary Key UserID. I only want to pull in tables that have a foreign key equal to a specific value.

The following query pulls in all the tables that have a foreign key to UserID

    USE information_schema;
SELECT TABLE_NAME
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'Users'
  AND REFERENCED_COLUMN_NAME = 'UserID'
  AND TABLE_SCHEMA = 'erik_rm12_working41_main';

I just need a way to check that the tables listed have a value in their foreign key equal to a specific value, say 26.

Upvotes: 0

Views: 470

Answers (1)

pyborg
pyborg

Reputation: 166

I am not able to understand your requirement clearly... Based on my understanding i given this query... You can try it and let us know, if anything required.

    SELECT f.name AS ForeignKey, 
    OBJECT_NAME(f.parent_object_id) AS TableName, 
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 

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

     WHERE OBJECT_NAME(f.parent_object_id)= '**<Table_Name>**'

Upvotes: 1

Related Questions