SQL Server: Script to Find Tables in Stored Procedures, That Are Without NonClustered Index

From my query bank here is another useful script which can help a Database Administrator to list down stored procedures with tables names which are used in stored procedures but don't contain and non clustered index.

	/*
	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

view raw TablesInSPsWithoutIndexes.sql hosted with ❤ by GitHub

Query Output: