Find the referenced table name using table, field and schema name
If you don't need this to be portable to another RDBMS it is much faster and simpler to use the catalog tables in pg_catalog
instead of the standard information schema:
SELECT c.confrelid::regclass::text AS referenced_table
, c.conname AS fk_name
, pg_get_constraintdef(c.oid) AS fk_definition
FROM pg_attribute a
JOIN pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
WHERE a.attrelid = '"Schema2"."TableB"'::regclass -- table name
AND a.attname = 'A_Id' -- column name
AND c.contype = 'f'
ORDER BY conrelid::regclass::text, contype DESC;
Returns:
referenced_table | fk_name | fk_definition
------------------+-------------------------+----------------------------------------------
Schema1.TableA | b1_fkey | FOREIGN KEY ("B_id") REFERENCES "Schema1"."TableA"("A_id")
Notes
The additional two columns are for orientation only. According to your Q, you only need the first column.
This returns all referenced tables by all foreign keys involving the given column name - including FK constraints on multiple columns.
The name is automatically schema-qualified or not according to the visibility set by the current
search_path
. The name is also escaped where needed (illegal or upper case characters, reserved words, ...) automatically, too.
Check out details of pg_constraint
and pg_attribute
in the manual. And more about object identifier types as well.
Related:
- PostgreSQL drop constraint with unknown name
- Retrieving all PK and FK
How can I list all foreign keys referencing a given table in SQL Server?
Not sure why no one suggested but I use sp_fkeys
to query foreign keys for a given table:
EXEC sp_fkeys 'TableName'
You can also specify the schema:
EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'
Without specifying the schema, the docs state the following:
If pktable_owner is not specified, the default table visibility rules
of the underlying DBMS apply.In SQL Server, if the current user owns a table with the specified
name, that table's columns are returned. If pktable_owner is not
specified and the current user does not own a table with the specified
pktable_name, the procedure looks for a table with the specified
pktable_name owned by the database owner. If one exists, that table's
columns are returned.
Find all table names with column name?
Please try the below query. Use sys.columns to get the details :-
SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyCol%';
How can I find out what FOREIGN KEY constraint references a table in SQL Server?
Here it is:
SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
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.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'YourTableName'
This way, you'll get the referencing table and column name.
Edited to use sys.tables instead of generic sys.objects as per comment suggestion.
Thanks, marc_s
Determine Which Objects Reference a Table in SQL Server
If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).
It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??
find sql table name with a particular column
In SQL Server, you can query sys.columns
.
Something like:
SELECT
t.name
FROM
sys.columns c
inner join
sys.tables t
on
c.object_id = t.object_id
WHERE
c.name = 'NameID'
You might want an additional lookup to resolve the schema name, if you have tables in multiple schemas.
How can I find which tables reference a given table in Oracle SQL Developer?
No. There is no such option available from Oracle SQL Developer.
You have to execute a query by hand or use other tool (For instance PLSQL Developer has such option). The following SQL is that one used by PLSQL Developer:
select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in ('P', 'U')
and table_name = :r_table_name
and owner = :r_owner
)
order by table_name, constraint_name
Where r_owner
is the schema, and r_table_name
is the table for which you are looking for references. The names are case sensitive
Be careful because on the reports tab of Oracle SQL Developer there is the option "All tables / Dependencies" this is from ALL_DEPENDENCIES which refers to "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links.". Then, this report have no value for your question.
How do I see all foreign keys to a table or column?
For a Table:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<database>' AND
REFERENCED_TABLE_NAME = '<table>';
For a Column:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<database>' AND
REFERENCED_TABLE_NAME = '<table>' AND
REFERENCED_COLUMN_NAME = '<column>';
Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.
Related Topics
SQL Oracle Left Join and Subquery Error: Ora-00905: Missing Keyword
Laravel Eloquent to Join Table and Count Related
Is There Any Other Way to Create Constraints During SQL Table Creation
Is Order by Clause Allowed in a Subquery
Order of Ands in Where Clause for Greatest Performance
SQL Query to Translate a List of Numbers Matched Against Several Ranges, to a List of Values
Why Can Pl/Pgsql Functions Have Side Effect, While SQL Functions Can'T
Which Is the Best Way to Form the String Value Using Column from a Table with Rows Having Same Id
How to Generate a Hierarchy Path in SQL That Leads to a Given Node
Differencein These Two Queries as Getting Two Different Result Set
Using Dynamic SQL to Specify a Column Name by Adding a Variable to Simple SQL Query
Dynamically Choose Column in SQL Query
Selecting Distinct Values for Multiple Columns
SQL Get "Iso Year" for Iso Week
How to Get Max(Date) from Given Set of Data Grouped by Some Fields Using Pyspark
Do Ddl Statements Always Give You an Implicit Commit, or Can You Get an Implicit Rollback