Footstep effects depends on the room index/number (first byte in room header, not the room header offset), the current area and occasionally other conditions.

Related ASM routine : $91:F0A5

-------------------------------
Crateria

   Room #0
   Effect : water splash
   Condition : Rain FX1

   Room #5
   Effect : water splash
   Condition : Samus Y position is > to #$03B0
   
   Room #7, #9, #10, #11, #12, #14
   Effect : water splash
   Condition : None

   Room #28
   Effect : dust cloud
   Condition : None

-------------------------------
Brinstar
   
   Room #5 to #8 and #11
   Effect : dust cloud
   Condition : None

-------------------------------
Norfair
   
   Any room #
   Effect : dust cloud
   Condition : None

-------------------------------
Wrecked Ship

   => See Norfair

-------------------------------
Maridia
   
   Any room #
   Effect : water splash
   Condition : None

-------------------------------
Tourian

   => See Brinstar

-------------------------------
Ceres & Debug

   => No footstep effect



Note1 : all room indexes listed above are decimal numbers
Note2 : footstep effects will not trigger in water/lava/acid, except if you've set the "line-shift" flag (water only).