Folks, thanks for the replies. I checked the plan of these queries and figured out that no matching rows were being returned. It didn't dawn on me that I was using top without order by. Does the following query give non-null values for enc_routine_name column?

PS: I do not have access to SSMS right now.

--Subquery with CTE - was surprised by the output

;WITH cteProcDependencies

AS

(

SELECT

TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]

FROM

INFORMATION_SCHEMA.routines r

INNER JOIN INFORMATION_SCHEMA.routines rdep

ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

order by r.ROUTINE_NAME, rdep.ROUTINE_NAME

)

SELECT

pd.ROUTINE_NAME,

STUFF((SELECT ', ' + enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]

FROM cteProcDependencies pd

GROUP BY pd.ROUTINE_NAME