sql 根据外键表和外键列得到主键表和主键列的方法

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

GO

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