需要帮助从SQL 2005查询中删除过时的=*连接运算符

=* 这里的要点是,即使找不到记录,也要给出结果 sc2 left join :

select distinct po.name , sc.name, sc2.name
from (sysobjects fo, sysobjects po, sysforeignkeys fk, sysobjects oo
    , syscolumns sc, sysreferences ref)
left join syscolumns sc2
  on sc2.id = po.id and sc2.colid = ref.fkey2
where fo.xtype = 'F' and oo.name = @tab_name
  and po.id = fo.parent_obj and fo.id = fk.constid and oo.id = fk.rkeyid
  and sc.id = po.id and ref.constid = fk.constid and sc.colid = ref.fkey1;

为了可读性,您也愿意将其余的旧式交叉连接重写为内部连接。您已经看到,旧式语法太难理解。

尝试此查询--

SELECT DISTINCT po.NAME
    ,sc.NAME
    ,sc2.NAME
FROM sysobjects fo
    ,sysobjects po
    ,sysforeignkeys fk
    ,sysobjects oo
    ,syscolumns sc
    ,sysreferences ref
    ,syscolumns sc2
WHERE fo.xtype = 'F'
    AND oo.NAME = @tab_name
    AND po.id = fo.parent_obj
    AND fo.id = fk.constid
    AND oo.id = fk.rkeyid
    AND sc.id = po.id
    AND ref.constid = fk.constid
    AND sc.colid = ref.fkey1
    AND sc2.id = Null -- sc2.id = * po.id 
    AND sc2.colid = Null -- sc2.colid = * ref.fkey2 

*= Left Outer Join 

=* Right Outer Join

在上面的查询中,我们试图通过搜索为null的ID来实现相同的目标。

我希望它能起作用。