@manhnguyenv

Welcome to my blog!

View Relationships SQL Server

November 30, 2019 02:13

Relationships of a given table (edit)

Tìm tên các bảng trong Stored Procedure:  https://manhng.com/blog/tables-in-stored-procedure/

Tìm quan hệ của một bảng với các bảng khác: https://manhng.com/blog/relationship/

https://stackoverflow.com/questions/2732356/list-of-all-tables-with-a-relationship-to-a-given-table-or-view

T-SQL

SELECT
fk.name 'FK Name',
tp.name 'Parent table',
cp.name, cp.column_id,
tr.name 'Refrenced table',
cr.name, cr.column_id
FROM 
sys.foreign_keys fk
INNER JOIN 
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
WHERE tp.name = 'Your_Table_Name'

You want to know all foreign key relationships

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('Your_Table_Name')
      OR parent_object_id = OBJECT_ID('Your_Table_Name');

COLUMNS

select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%TrangThai%'

For SQL Server 2005

SELECT
    name, OBJECT_NAME(parent_object_id) 'Table'
FROM 
    sys.foreign_keys
WHERE 
    referenced_object_id = OBJECT_ID('Your_Table_Name')

Categories

Recent posts