Feature: Join to self

While the popup shows foreign key - primary key pairs for almost all tables that could be joined to the one in the query after typing JOIN it does not show the most trivial one : itself. Would it be possible to include that in the popup?

I think you can customize the Joins query in the Options to have SQL Assistant return the base table too. This will make the popup to show self-joins

Though it was a bit tricky, I managed to get it. Thanks for the tip.

Would you mind posting your query so others can reuse?

Sure, I should have done that without asking, sorry. I edited the Joins section for MSSQL under DB Queries on DB Options tab. I only made it available for MSSQL 2005/2008 because I don't want to use 2000 or MSDE anymore but I think it can be adapted for that one just as easily (if it needs modification at all).
In case you haven't modified that part yet you can just copy paste it over the old one. After creating a backup, of course :)
So far it seems to be working. If you find any errors in it, let me know, please.

Code:

if @@version like 'Microsoft SQL Server 2005%'
or @@version like 'Microsoft SQL Server 2008%'

select
(select name from /*db.*/dbo.sysobjects where id = f1.fkeyid),
(select name from /*db.*/dbo.syscolumns where id = f1.fkeyid and colid = f1.fkey),
(select name from /*db.*/dbo.syscolumns where id = f1.rkeyid and colid = f1.rkey),
(select s.name from /*db.*/sys.all_objects o, /*db.*/sys.schemas s where s.schema_id = o.schema_id and o.object_id = f1.fkeyid),
'P:' + CAST(constid AS VARCHAR)
from
/*db.*/dbo.sysforeignkeys f1
where
rkeyid = :OBJECT_ID
union all
select
(select name from /*db.*/dbo.sysobjects where id = f2.rkeyid),
(select name from /*db.*/dbo.syscolumns where id = f2.rkeyid and colid = f2.rkey),
(select name from /*db.*/dbo.syscolumns where id = f2.fkeyid and colid = f2.fkey),
(select s.name from /*db.*/sys.all_objects o, /*db.*/sys.schemas s where s.schema_id = o.schema_id and o.object_id = f2.rkeyid),
'F:' + CAST(constid AS VARCHAR)
from
/*db.*/dbo.sysforeignkeys f2
where
fkeyid = :OBJECT_ID

/*self join extension starts*/
UNION ALL
SELECT
so.[name]
,ac.[name]
,ac.[name]
,(select s.name from /*db.*/sys.all_objects o, /*db.*/sys.schemas s where s.schema_id = o.schema_id and o.object_id = so.id)
,'P:' + CAST(so.id AS VARCHAR)
FROM
dbo.sysobjects AS so
INNER JOIN
sys.all_columns AS ac
   ON ac.[object_id] = so.id
INNER JOIN
/*db.*/sys.indexes i
   ON i.[object_id] = ac.[object_id]
AND i.is_primary_key != 0
INNER JOIN
/*db.*/sys.index_columns k
   ON k.[object_id] = i.[object_id]
      AND k.index_id = i.index_id
      AND k.[object_id] = ac.[object_id]
      AND k.column_id = ac.column_id
WHERE so.id = :OBJECT_ID
/*self join extension ends*/
order by 1

else

select
(select name from /*db.*/dbo.sysobjects where id = f1.fkeyid),
(select name from /*db.*/dbo.syscolumns where id = f1.fkeyid and colid = f1.fkey),
(select name from /*db.*/dbo.syscolumns where id = f1.rkeyid and colid = f1.rkey),
(select u.name from /*db.*/dbo.sysusers u, /*db.*/dbo.sysobjects o where u.uid = o.uid and o.id = f1.fkeyid),
'P:' + CAST(constid AS VARCHAR)
from
/*db.*/dbo.sysforeignkeys f1
where
rkeyid = :OBJECT_ID
union all
select
(select name from /*db.*/dbo.sysobjects where id = f2.rkeyid),
(select name from /*db.*/dbo.syscolumns where id = f2.rkeyid and colid = f2.rkey),
(select name from /*db.*/dbo.syscolumns where id = f2.fkeyid and colid = f2.fkey),
(select u.name from /*db.*/dbo.sysusers u, /*db.*/dbo.sysobjects o where u.uid = o.uid and o.id = f2.rkeyid),
'F:' + CAST(constid AS VARCHAR)
from
/*db.*/dbo.sysforeignkeys f2
where
fkeyid = :OBJECT_ID
order by 1

EDIT: sorry, there is no formatting applied.

Thank you very much for sharing this tip!