OK, so I'd like to confirm something, and then toss out a variation on Steve's fill factor script.
Fill factor 0 = 100% full on each page (no free space) so adding ANY new rows to the table will cause a page split, correct?
As for the script, some of us may not know which index names go where, so it was easy enough to join up the results from Steve's script to sys.objects:
, SI.name as 'Index Name'
, so.name as 'Table Name'
, SI.type_desc as 'Index Type'
from sys.indexes as SI
inner join sys.objects as SO
on SO.object_id = SI.object_id
where SI.type_desc <> 'HEAP'
and SO.type_desc <> 'SYSTEM_TABLE'
and fill_factor > 0
order by SO.name
As you can see, I set it to leave out Heaps and System tables from the results.
Boneheaded, I should know all my indexes by name? ;-)