Your query has all sorts of problems, and I am not sure that it is even meaningful.
You are making a full join, which as such means that you will get all rows in sys.dm_exec_query_stats and sys.sysprocesses and on the rows where there is no match on the join conditions, you will get NULL for the columns for the other view.
But then you have the filter
where spid <> @@spid
Which means that you filter out the rows where the sql_handle does not exist in sys.sysprocesses. (Because when p.spid is null, the condition evaluates to UNKNOWN, not to TRUE.)
At the same time, you are only passing q.sql_handle to sys.dm_exec_sql_text, so if this is NULL, you get nothing back obviously.
So if we only look at this from the point of view of how to write a full join properly, the query should be:
select (select text from sys.dm_exec_sql_text(isnull(q.sql_handle, p.sql_handle))) as [Script Text],
DB_NAME(dbid) as [Database],
program_name,
nt_username,
loginame,
last_execution_time
from sys.dm_exec_query_stats q
full join (SELECT * FROM sys.sysprocesses WHERE spid <> @@spid) p on q.sql_handle = p.sql_handle
order by last_execution_time desc
But when I ran this query, I got this error:
Msg 569, Level 16, State 4, Line 54
The handle that was passed to dm_exec_sql_text was invalid.
This is because sys.sysprocesses has the handle of the query more recently executed, but the plan for that query may no longer be in cache. And then there a lot of system processes of which the handle is 0x0.
Also, sys.sysprocesses is an old compatibility view which you should not use, but rather you should use sys.dm_exec_sessions, sys.dm_exec_connections or sys.dm_exec_requests, a little depending on what you want find.
At the same time sys.dm_exec_query_stats holds all plans currently in the cache, no matter if there is a process executing the query right now.
But it all boils down to what you really want to achieve.