Month: March 2009

I’m in the need of a function that will find the parent table based on a child table name and a column name.  The column should be a foreign key.  I tried to find a solution on the web, but couldn’t find anything.  I probably used the wrong terminology in the searches.  So this is what I have come up with.


'CREATED FUNCTION fn_GetParentTable'
GO
CREATE
FUNCTION dbo.fn_GetParentTable(
     @ChildTable VARCHAR(200)
     ,@ChildColumn VARCHAR(200)
RETURNS
VARCHAR(MAX)
BEGIN
     DECLARE @ParentTableName VARCHAR(MAX)

     SELECT @ParentTableName = Parent_Table_Constraints.Table_Name
     FROM Information_Schema.Table_Constraints Table_Constraints
     INNER JOIN Information_Schema.CONSTRAINT_COLUMN_USAGE CONSTRAINT_COLUMN_USAGE ON
     Table_Constraints.Constraint_Name = CONSTRAINT_COLUMN_USAGE.Constraint_Name
     INNER JOIN Information_Schema.REFERENTIAL_CONSTRAINTS REFERENTIAL_CONSTRAINTS ON
  CONSTRAINT_COLUMN_USAGE.Constraint_Name = REFERENTIAL_CONSTRAINTS.Constraint_Name
     INNER JOIN Information_Schema.Table_Constraints Parent_Table_Constraints ON
  REFERENTIAL_CONSTRAINTS.Unique_Constraint_Name = Parent_Table_Constraints.Constraint_Name
     WHERE
  CONSTRAINT_COLUMN_USAGE.Table_Name = @ChildTable
  AND CONSTRAINT_COLUMN_USAGE.Column_Name = @ChildColumn
  AND Table_Constraints.Constraint_Type = 'FOREIGN KEY'
     RETURN @ParentTableName
END
GO



SELECT dbo.fn_GetParentTable ('tableName', 'ColumnName')