Finding indexes in a Sql Server database

I am currently working on a system which uses a Sql Server database containing hundreds of tables. I find that querying the system tables directly to identify schema information can be more productive than digging around in Management Studio.

Here is a query I ran recently to identify the indexes on certain tables.

SELECT DB_NAME() AS Database_Name , sc.name AS Schema_Name , o.name AS Table_Name , i.name AS Index_Name , i.type_desc AS Index_Type FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id WHERE i.name IS NOT NULL AND o.type = 'U' ORDER BY o.name, i.type

I am posting it here for your reference (and mine!). I hope you find it helpful.