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
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