sql 根据外键表和外键列得到主键表和主键列的方法-优快云博客

本文提供了一个SQL函数，用于从外键关联中获取主键列名。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Create FUNCTION [dbo].[GetPrimaryColumn] ( -- Add the parameters for the function here @column nvarchar(500) ) RETURNS nvarchar(max) AS

begin -- Add the SELECT statement with parameter references here declare @result nvarchar(max)='' select @result=(PrimaryTableName +'.'+ PrimaryColumnName)from ( SELECT ForeignTableID = b.fkeyid , ForeignTableName = '['+object_name(b.fkeyid) +']' , ForeignColumnID = b.fkey , ForeignColumnName ='[' + (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) +']', PrimaryTableID = b.rkeyid , PrimaryTableName = '['+object_name(b.rkeyid)+']' , PrimaryColumnID = b.rkey , PrimaryColumnName ='['+ (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) +']' , CascadeUpdate = ObjectProperty(a.id,'CnstIsUpdateCascade') , CascadeDelete = ObjectProperty(a.id,'CnstIsDeleteCascade') FROM sysobjects a join sysforeignkeys b on a.id = b.constid join sysobjects c on a.parent_obj = c.id where a.xtype = 'f' AND c.xtype = 'U' ) FPT where (FPT.ForeignTableName +'.' +FPT.ForeignColumnName)= @column

return @result end

转载于:https://www.cnblogs.com/Mac_Hui/archive/2013/02/05/2892535.html