Getting Table and Index names for fragmented indexes in SQL Server

To find table and index names for fragmented indexes with fragmentation larger than 20% and page count larger than 100 pages (run in the database for which you want to see the list of fragmented indexes):
SELECT
OBJECT_NAME(object_id) AS Table_Name
,(SELECT name FROM sys.indexes WHERE object_id = i.object_id and index_id = i.index_id) as Index_Name
,avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) i
WHERE avg_fragmentation_in_percent > 20 and page_count > 100
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
ORDER BY avg_fragmentation_in_percent DESC
For a more detailed result with database name and schema name for fragmented indexes with fragmentation larger than 20% and page count larger than 100 pages (run in the database for which you want to see the list of fragmented indexes):
SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, ob.name AS Table_Name
, ind.name AS Index_Name
, pt.avg_fragmentation_in_percent
, pt.fragment_count
, pt.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'limited') pt
INNER JOIN sys.objects ob ON pt.object_id = ob.object_id
INNER JOIN sys.schemas sc ON ob.schema_id = sc.schema_id
INNER JOIN sys.indexes ind ON pt.object_id = ind.object_id AND pt.index_id = ind.index_id
WHERE pt.index_id > 0
AND pt.avg_fragmentation_in_percent > 20
AND pt.page_count > 100
ORDER BY avg_fragmentation_in_percent DESC
If you get error like this when running one of the codes:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ‘)’.
…you can replace DB_ID() with the database ID in the query that returns the error message.
To find out the database ID:
USE Database_Name
SELECT DB_ID() as DB_ID;
GO

To see how to list all indexes for all tables in a SQL Server database read related article:
List all indexes for all tables in a SQL Server database