Index and Table size across the Server

Another place to start, if you need to use this as a learning exercise, and you're using a more recent version of SQL Server (2005 and above), is http://msdn.microsoft.com/en-us/library/ms188917.aspx - the DMV for physical index statistics, which includes the number of (8k) pages used by an index.

Try using this: http://www.atlantis-interactive.co.uk/products/dataspaceanalyser/default.aspx

Please use given below query-

DECLARE @sql VARCHAR(200) DECLARE @SourceDB VARCHAR(50) CREATE TABLE #ServerSpaceUsed (DB_Name VARCHAR(100),Table_Name VARCHAR(128), Rows VARCHAR(11), Reserved VARCHAR(18),Data VARCHAR(18),Index_size VARCHAR(18),Unused VARCHAR(18))

DECLARE CUR_SR_SPACE CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE name NOT IN('master','tempdb','model','msdb') OPEN CUR_SR_SPACE FETCH NEXT FROM CUR_SR_SPACE INTO @SourceDB WHILE @@FETCH_STATUS =0 BEGIN

CREATE TABLE #tables(name VARCHAR(128))

SET @sql = 'INSERT #tables SELECT TABLE_NAME FROM ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''' EXEC(@sql)

 DECLARE @NAME VARCHAR(128)
 SET @name = ''
 
 CREATE TABLE #DBSpaceUsed 
     (Table_Name VARCHAR(128), Rows VARCHAR(11), 
         Reserved VARCHAR(18),Data VARCHAR(18),Index_size VARCHAR(18),Unused VARCHAR(18))
 
 WHILE EXISTS (SELECT * FROM #tables WHERE name > @name)
 BEGIN
 
     SELECT @name = MIN(name) FROM #tables WHERE name > @name
     SELECT @sql = 'EXEC ' + @SourceDB + '..sp_executesql N''INSERT #DBSpaceUsed exec sp_spaceused ' + @name + ''''
     EXEC (@sql)
 END
 
 INSERT INTO #ServerSpaceUsed(DB_Name,Table_Name,Rows,Reserved,Data,Index_size,Unused)
 SELECT @SourceDB,Table_Name,Rows,Reserved,Data,Index_size,Unused FROM #DBSpaceUsed
 
 drop table #tables
 drop table #DBSpaceUsed

FETCH NEXT FROM CUR_SR_SPACE INTO @SourceDB END CLOSE CUR_SR_SPACE DEALLOCATE CUR_SR_SPACE

SELECT * FROM #ServerSpaceUsed DROP TABLE #ServerSpaceUsed

http://blog.sqlauthority.com/2009/11/18/sql-server-size-of-index-table-a-puzzle-to-find-index-size-for-each-index-on-table/