In SQL Server is there a command to return a list of all tables with a relationship to a given table or view?
EDIT: SQL SERVER 2008
A view is a database object that allows generating a logical subset of data from one or more tables. A table is a database object or an entity that stores the data of a database. The view depends on the table. The table is an independent data object.
The easiest way to find all tables in SQL is to query the INFORMATION_SCHEMA views. You do this by specifying the information schema, then the “tables” view. Here's an example. SELECT table_name, table_schema, table_type FROM information_schema.
Using SQL Server Management Studio Open the Table Designer for the table containing the foreign key you want to view, right-click in the Table Designer, and choose Relationships from the shortcut menu. In the Foreign Key Relationships dialog box, select the relationship with properties you want to view.
For SQL Server 2005 and up, use something like:
SELECT
name, OBJECT_NAME(parent_object_id) 'Table'
FROM
sys.foreign_keys
WHERE
referenced_object_id = OBJECT_ID('Your-referenced-table-name-here')
-- To find all the foreign keys established to a table!
-- Columns: FKTABLE_NAME, FKCOLUMN_NAME
sp_fkeys @pktable_name='your table name here'
Say your table name is TableX. If you want to know all foreign key relationships (columns of TableX referenced in other tables and columns of other tables referenced in TableX) you could do this:
select name 'ForeignKeyName',
OBJECT_NAME(referenced_object_id) 'RefrencedTable',
OBJECT_NAME(parent_object_id) 'ParentTable'
from sys.foreign_keys
where referenced_object_id = OBJECT_ID('TableX') or
parent_object_id = OBJECT_ID('TableX')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With