One important step of database maintenance is maintaining indexes, rebuilding or defragmenting them at scheduled intervals.
This step is obvious to everyone, but has an “hidden” requirements that sometimes is underestimated. Indexes MUST exists!
I these last days I’ve been working on a “problematic” 
database server and one of the script that helped be to understand how big was the problem, is a simple script that shows, for each table, if it has a clustered index or nor and if it has nonclustered indexes.
Thanks to this script it’s very easy to find all the tables (along with the number of rows contained within) that doesn’t have an index at all, so you can explain to the customer that their performance problems are not related to poor index maintenance or badly written queries (which, of course, on a database without indexes are somehow expected…), but, in first place, by the total absence of indexes!
Here’s the script:
with cte as
(
select
table_name = o.name,
o.[object_id],
i.index_id,
i.type,
i.type_desc
from
sys.indexes i
inner join
sys.objects o on i.[object_id] = o.[object_id]
where
o.type in ('U')
and
o.is_ms_shipped = 0 and i.is_disabled = 0 and i.is_hypothetical = 0
and
i.type <= 2
), cte2 as
(
select
*
from
cte c
pivot
(count(type) for type_desc in ([HEAP], [CLUSTERED], [NONCLUSTERED])) pv
)
select
c2.table_name,
[rows] = max(p.rows),
is_heap = sum([HEAP]),
is_clustered = sum([CLUSTERED]),
num_of_nonclustered = sum([NONCLUSTERED])
from
cte2 c2
inner join
sys.partitions p on c2.[object_id] = p.[object_id] and c2.index_id = p.index_id
group by
table_name
As usual the script can be found also in the sys2 collection on CodePlex:
http://sys2dmvs.codeplex.com/