CODEWITHSUNDEEP
sql-server
nicholaswmin
nicholaswmin

Reputation: 22939

Getting Referenced tables in SQL Server

I have a list of foreign keys. I'd like to find out the tables where these FK's point to and the actual key the point to.

I've got a list of FK's like so:

columnName0, columnName1, columnName2

Foreign key references

Sample tables:

Table0:

idTable0, PK
name

Table1:

idTable1, PK
age

Table2:

idTable2, PK
createdOn

A sample result:

| column      | referenced_column | referenced_table |
|-------------|-------------------|------------------|
| columnName0 | idTable0          | table0           |
| columnName1 | idTable1          | table1           |
| columnName2 | idTable2          | table2           |

I'm trying to translate something I do in MySQL like this:

SELECT DISTINCT
    COLUMN_NAME AS column,
    REFERENCED_COLUMN_NAME AS referenced_column,
    REFERENCED_TABLE_NAME AS referenced_table
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    COLUMN_NAME IN (?);

Note about the duplicate mark

I'm going to have to use straight-up queries (no stored procedures).

I'll translate the query to Knex.js code which doesn't really play well with stored procedures.

Upvotes: 0

Views: 99

Answers (1)

pmbAustin
pmbAustin

Reputation: 3970

Not sure if this is exactly what you want, but this query returns all FKs by name, the table and column the key refers to, and the referenced table and column. You can add appropriate WHERE filters or ORDER BY clauses as you desire:

SELECT fk.[name], OBJECT_NAME(fk.parent_object_id) AS TableName, c.[name] AS ColumnName, OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable, rc.[name] AS ReferencedColumnName
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns kc ON fk.object_id = kc.constraint_object_id 
INNER JOIN sys.columns c ON kc.parent_object_id = c.object_id AND kc.parent_column_id = c.column_id
INNER JOIN sys.columns rc ON kc.referenced_object_id = rc.object_id AND kc.referenced_column_id = rc.column_id

Upvotes: 1

Related Questions