tsql - 获取具有唯一外键的表之间关系的表名

Question

我有一个带有 ResId 主键的主表。

ResId 是 200 多个表的外键。在这些表中插入具有唯一值的 ResId。

例如表1中的ResId是1,7,50，表2中是3,4,21，表3中是 2,6,81 , ...

如何从主表中获取具有特定 ResId 的表名。

例如当ResId=3结果是table2，当ResId=6结果是table3

Answer 1

Declare @ResourceWorkflowID Int = 8019--For Example
Declare @Temp Table
(
    TblName Varchar(100),
    RowNum Int Identity(1,1)
)
Insert Into @Temp
    Select Distinct Object_Name(F.Parent_Object_Id) AS TableName
    From Sys.Foreign_Keys AS F
    Inner Join Sys.Foreign_Key_Columns As Fc On F.Object_Id = Fc.Constraint_Object_Id
        Where Col_Name(Fc.Parent_Object_Id, Fc.Parent_Column_Id) = 'ResourceWorkflowID'
Declare @Counter Int = 1, @State Int = 0
While(@Counter <= (Select Max(RowNum) From @Temp) And @State = 0)
Begin
    Declare @Str nVarchar(400) = '', @Name nVarchar(50) = ''
    Select @Name = TblName From @Temp Where RowNum = @Counter
    Set @Str = 'If Exists (Select ResourceWorkflowID From '+@Name+' Where ResourceWorkflowID = '+Cast(@ResourceWorkflowID As Nvarchar(10))+')
        Set @Cnt = ' + Cast(@Counter As Nvarchar(2))
    Exec Sp_ExecuteSql @Str,N'@Cnt Int Output', @Cnt=@State Output
    Set @Counter += 1
End
Select TblName As TableName From @Temp Where RowNum = @State
Go