quote:

---

Originally posted by chadmat

This will work for the current database


select *
from sys.objects o join sys.indexes i on o.parent_object_id = i.object_id
where o.type = 'f'
AND i.type_desc = 'HEAP'
AND o.object_id NOT IN (SELECT o.object_id FROM sys.objects
join sys.indexes i on o.parent_object_id = i.object_id
where o.type = 'f'
AND i.type_desc = 'NONCLUSTERED')

-Chad

---

EXEC sp_Msforeachdb 'select * 
from sys.objects o join sys.indexes i on o.parent_object_id = i.object_id
where o.type = ''f'' 
AND i.type_desc = ''HEAP''
AND o.object_id NOT IN (SELECT o.object_id FROM sys.objects 
join sys.indexes i on o.parent_object_id = i.object_id
where o.type = ''f'' 
AND i.type_desc = ''NONCLUSTERED'')'

quote:

---

Originally posted by visakh16

use sp_Msforeachdb for looping through dbs

try like

EXEC sp_Msforeachdb 'select * 
from sys.objects o join sys.indexes i on o.parent_object_id = i.object_id
where o.type = ''f'' 
AND i.type_desc = ''HEAP''
AND o.object_id NOT IN (SELECT o.object_id FROM sys.objects 
join sys.indexes i on o.parent_object_id = i.object_id
where o.type = ''f'' 
AND i.type_desc = ''NONCLUSTERED'')'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---