1 ;WITH NonClustedIndexColumn AS 2 ( 3 SELECT dataid,column_id,c.object_id, MAX(c.dataneme) dataneme FROM 4 sys.index_columns d 5 INNER JOIN (SELECT index_id dataid,name dataneme ,* FROM sys.indexes WHERE index_id > 1 6 UNION ALL 7 SELECT a.index_id dataid,a.name dataneme, b.* FROM sys.indexes a 8 INNER JOIN sys.indexes b ON a.object_id = b.object_id AND b.index_id = 1 9 ) c ON c.object_id = d.object_id AND d.index_id = c.index_id 10 GROUP BY dataid,column_id,c.object_id 11 ),NonClustedIndexSize 12 AS( 13 SELECT --a.name,a.max_length,b.name,c.index_id 14 OBJECT_NAME(a.object_id) name ,a.object_id,MAX(c.dataneme) index_name, c.dataid 15 ,SUM(CASE WHEN b.name IN ('varchar','nvarchar') THEN 2+a.max_length ELSE 0 END) [Variable_Data] 16 ,SUM(CASE WHEN b.name not IN ('varchar','nvarchar') THEN a.max_length ELSE 0 END) [Fixed_Data] 17 ,CEILING(SUM(CASE WHEN a.is_nullable = 1 THEN 1 ELSE 0 END)*1.0 /8) [NullByte] 18 ,CASE WHEN c.dataid <=1 THEN 2 ELSE 1 END [RowHeader] 19 ,CASE WHEN c.dataid <=1 THEN 2 ELSE 0 END [Fixed_Length] 20 ,MAX( CASE WHEN b.name IN ('varchar','nvarchar') THEN 2 ELSE 0 END) [Variable_Length] 21 , 2 [Column_Count] 22 FROM sys.columns a 23 INNER JOIN sys.types b ON a.user_type_id = b.user_type_id 24 INNER JOIN NonClustedIndexColumn c ON a.object_id = c.object_id AND a.column_id = c.column_id 25 WHERE a.object_id >100 AND dataid > 1 -- AND OBJECT_NAME(a.object_id) = 'userorder_package' 26 GROUP BY a.object_id,c.dataid 27 ) 28 , ClustedHeapSize AS( 29 SELECT --a.name,a.max_length,b.name,c.index_id 30 OBJECT_NAME(a.object_id) name ,a.object_id,MAX(c.name) index_name, c.index_id 31 ,SUM(CASE WHEN b.name IN ('varchar','nvarchar') THEN 2+a.max_length ELSE 0 END) [Variable_Data] 32 ,SUM(CASE WHEN b.name not IN ('varchar','nvarchar') THEN a.max_length ELSE 0 END) [Fixed_Data] 33 ,CEILING(SUM(CASE WHEN a.is_nullable = 1 THEN 1 ELSE 0 END)*1.0 /8) [NullByte] 34 ,CASE WHEN c.index_id <=1 THEN 2 ELSE 1 END [RowHeader] 35 ,CASE WHEN c.index_id <=1 THEN 2 ELSE 0 END [Fixed_Length] 36 ,MAX( CASE WHEN b.name IN ('varchar','nvarchar') THEN 2 ELSE 0 END) [Variable_Length] 37 , 2 [Column_Count] 38 FROM sys.columns a 39 INNER JOIN sys.types b ON a.user_type_id = b.user_type_id 40 INNER JOIN sys.indexes c ON c.object_id = a.object_id --AND d.index_id = c.index_id 41 WHERE a.object_id >100 AND c.index_id <= 1 42 GROUP BY a.object_id,c.index_id ) 43 ,RowSize AS 44 ( 45 SELECT name = MAX(name),OBJECT_ID,rowsize=SUM([Variable_Data]+[Fixed_Data]+[NullByte]+[RowHeader]+[Fixed_Length]+[Variable_Length]+[Column_Count]) 46 FROM ( 47 SELECT * FROM NonClustedIndexSize 48 UNION ALL 49 SELECT * FROM ClustedHeapSize 50 ) a 51 GROUP BY OBJECT_ID 52 ) 53 54 SELECT * FROM rowsize
