-- List all database tables and there indexes with -- detailed information about row count and -- used + reserved data space. SELECT SCH.NAME AS SchemaName, OBJ.NAME AS ObjName, OBJ.type_desc AS ObjType, INDX.NAME AS IndexName, INDX.type_desc AS IndexType, PART.partition_number AS PartitionNumber, PART.ROWS AS PartitionRows, STAT.ROW_COUNT AS StatRowCount, STAT.used_page_count * 8 AS UsedSizeKB, STAT.reserved_page_count * 8 AS RevervedSizeKB, CONCAT('UPDATE STATISTICS AtlantisDB.[', SCH.NAME, '].[', OBJ.NAME,'] [', INDX.NAME, '] WITH FULLSCAN GO') as UpdStat
FROM sys.partitions AS PART INNER JOIN sys.dm_db_partition_stats AS STAT ON PART.partition_id = STAT.partition_id AND PART.partition_number = STAT.partition_number INNER JOIN sys.objects AS OBJ ON STAT.object_id = OBJ.object_id INNER JOIN sys.schemas AS SCH ON OBJ.schema_id = SCH.schema_id INNER JOIN sys.indexes AS INDX ON STAT.object_id = INDX.object_id AND STAT.index_id = INDX.index_id WHERE stat.row_count<50000 AND INDX.type_desc != 'HEAP' AND SCH.name !='sys' ORDER BY row_count, SCH.NAME, OBJ.NAME, INDX.NAME, PART.partition_number
