There is many ways to get his information; however in most cases you get information for a single table. If you want to get it
Tag: sys.tables
Getting Index count for all tables
Davide Mauri wrote a nice script, I have done similar work without using CTE. So I really liked the script; I notice in his comments
SQL Canada (SQLCAN) 