Monday, 22 July 2013

SQL Query to Get List of All Referenced Tables for a Given Table

Use below SP to sql query to get list of all referenced/Parent/Master tables for a given table

CREATE PROCEDURE spGetAllReferencedTablesForTable
(
    @ReferencingTableName nvarchar(255) = 'Fact_BS'
)
AS
BEGIN
    SELECT
        c.CONSTRAINT_NAME
        , cu.TABLE_NAME AS ReferencingTable
        , cu.COLUMN_NAME AS ReferencingColumn
        , ku.TABLE_NAME AS ReferencedTable
        , ku.COLUMN_NAME AS ReferencedColumn
        , cu.TABLE_NAME + '.' + cu.COLUMN_NAME + ' = ' + ku.TABLE_NAME + '.' + ku.COLUMN_NAME AS JoinCondition
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
    ON cu.CONSTRAINT_NAME = c.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
    ON ku.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
    WHERE  Cu.TABLE_NAME = @ReferencingTableName
END

No comments:

Post a Comment