I wanted to chime in a shed some light on this issue since I work for an insurance brokerage. Please first note that coverages and exclusions vary from insurance company to insurance company so this is just general info and as far as varying from state to state who knows.
So in CA:
Your contents coverage on your homeowners policy will cover your tank, equiptment, etc. If you have your items scheduled then you will not have to pay your deductible, however if they are not then you will pay the deductible first. Depending on how much your tank and all that is worth it may not make sense to schedule it.
Now your live stock. Animals and pets, etc. arent covered in a homeowners policy. So for example if your dog gets hit by a car the insurance company doesnt give you money to buy a new one. So with your fish and all that wouldnt be covered. There is a very easy way around this but that may or may not be insrance fraud so I do not wish to say anymore about that.
As far as damage caused to your home by a water spill:
Most carriers (insurance companys) are going to cover any water damage done by household thigns like sinks, water pipes, and fish tanks. The flood policy's that people buy seperately from a home policy are for non-household thigns like natural disasters or excessive rainstorms etc.
I am not sure why FISHNU is saying his tank isnt covered, I will have to read his linked post.
Please remember this is general info and varies from state to state and insuracne company to insurance company