Script to get list of stored procedures which contains tables but without non clusterd indexes

TablesInSPsWithoutIndexes.sql

/*

Script By: Aasim Abdullah for http://connectsql.blogspot.com
Purpose: To get list of stored procedures which contains tables but without non clusterd indexes

*/


SELECT  *
FROM    ( SELECT    SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
    SUBSTRING(( SELECT DISTINCT
                        ', ' + OBJDEP.NAME
                FROM    sysdepends
                        INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
                        INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
                WHERE   obj.type = 'P'
                        AND Objdep.type IN ( 'U', 'V' ) --view and tables
                        AND EXISTS ( SELECT 1
                                     FROM   sys.indexes
                                     WHERE  type = 2
                                            AND Objdep.OBJECT_ID = OBJECT_ID )
                        AND sysdepends.id = procs.object_id 
              FOR
                XML PATH('') ), 2, 8000) AS TablesWithNCIndexes,
    SUBSTRING(( SELECT DISTINCT
                        ', ' + OBJDEP.NAME
                FROM    sysdepends
                        INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
                        INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
                WHERE   obj.type = 'P'
                        AND Objdep.type IN ( 'U', 'V' ) --view and tables
                        AND NOT EXISTS ( SELECT 1
                                         FROM   sys.indexes
                                         WHERE  type = 2
                                                AND Objdep.OBJECT_ID = OBJECT_ID )
                        AND sysdepends.id = procs.object_id 
              FOR
                XML PATH('') ), 2, 8000) AS TablesWithOutNCIndexes
          FROM      sys.procedures procs
          WHERE     procs.name NOT LIKE 'sp_%' --skip system stored procedures
          ) InnerTab
WHERE   TablesWithOutNCIndexes IS NOT NULL
        OR TablesWithNCIndexes IS NOT NULL