QUERY 1: - check fragmentation

declare @dbid int
select @dbid = db_id()
SELECT OBJECT_NAME(a.object_id) AS table_name,isnull(b.name,'No Index') AS index_name, index_type_desc AS index_type,
index_depth,index_level,page_count,avg_fragmentation_in_percent AS fragmentation 
from  
sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL , 'DETAILED') a
INNER JOIN sys.indexes b  
ON a.object_id = b.object_id AND a.index_id = b.index_id
and avg_fragmentation_in_percent > 30
and page_count > 10 
and index_type_desc <> 'HEAP'
QUERY 2: - rebuild indexes

Print 'Rebuilding indexes on every table in the database.'
EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?'",
@command2 = "ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)"
GO
Print 'Rebuilding indexes complete.'
Print 'Showing Results...'
SELECT 
  DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
,SI.NAME AS IndexName
,DPS.INDEX_TYPE_DESC AS IndexType
,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI 
    ON DPS.OBJECT_ID = SI.ID 
    AND DPS.INDEX_ID = SI.INDID
where dps.index_type_desc <> 'HEAP'
ORDER BY OBJECT_NAME(DPS.OBJECT_ID),DPS.avg_fragmentation_in_percent DESC
GO
Print 'Finished.'