List size of tables and indexes over all databases

list_sizes.sql

#https://www.sqlservercentral.com/Forums/Topic1479951-392-1.aspx
IF OBJECT_ID('tempdb..#indexInfo') IS NOT NULL
   DROP TABLE #indexInfo;

SELECT TOP 0
      t.TABLE_CATALOG AS db,
      t.TABLE_SCHEMA AS SchemaName,
      OBJECT_NAME(i.OBJECT_ID) AS TableName,
      ISNULL(i.name,'<HEAP>') AS IndexName,
      i.index_id AS IndexID,
      8 * SUM(a.used_pages) AS 'Indexsize(KB)'
INTO #indexInfo
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON OBJECT_NAME(i.OBJECT_ID)=t.TABLE_NAME
WHERE t.TABLE_SCHEMA IS NOT NULL
GROUP BY t.TABLE_CATALOG, t.TABLE_SCHEMA, i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id;

EXEC sp_msforeachdb '
USE [?];

INSERT INTO #indexInfo
   SELECT t.TABLE_CATALOG AS db,
         t.TABLE_SCHEMA AS SchemaName,
         OBJECT_NAME(i.OBJECT_ID) AS TableName,
         ISNULL(i.name,''<HEAP>'') AS IndexName,
         i.index_id AS IndexID,
         8 * SUM(a.used_pages) AS ''Indexsize(KB)''
   FROM [?].sys.indexes AS i
   JOIN [?].sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
   JOIN [?].sys.allocation_units AS a ON a.container_id = p.partition_id
   LEFT JOIN [?].INFORMATION_SCHEMA.TABLES t ON OBJECT_NAME(i.OBJECT_ID)=t.TABLE_NAME
   WHERE t.TABLE_SCHEMA IS NOT NULL
   GROUP BY t.TABLE_CATALOG, t.TABLE_SCHEMA, i.OBJECT_ID,i.index_id,i.name
   ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id;';

SELECT    ISNULL(db,'<ALL DB>') AS db, 
      SchemaName, 
      TableName, 
      IndexName, 
      IndexID, 
      [indexsize(KB)]
FROM #indexInfo
WHERE [Indexsize(KB)]<>0
ORDER BY db,SchemaName,TableName,IndexName,IndexID;