-- TRUNCATE TABLE interface.T_Log;
-- TRUNCATE TABLE T_COR_Log;
-- TRUNCATE TABLE T_LOG_ProzedurRun;
-- TRUNCATE TABLE T_RPT_LOG_ProzedurRun;
-- TRUNCATE TABLE T_COR_Error;
-- TRUNCATE TABLE T_SessionValues;
-- TRUNCATE TABLE VWSsessions;

-- sp_helptext 'sp_spaceused';
-- sp_spaceused 'T_SYS_Geschossrechte';


SELECT
     sch.name
    ,TableProperties.name AS TableName
    ,PartitionProperties.rows AS RowCounts
    ,SUM(AllocUnits.total_pages) * 8 AS TotalSpaceKB
    ,FORMAT( SUM(AllocUnits.total_pages) * 8 ,'N0', 'de-CH') + ' kB' AS TotalSpaceKB_Swiss
    ,SUM(AllocUnits.used_pages) * 8 AS UsedSpaceKB
    ,(SUM(AllocUnits.total_pages) - SUM(AllocUnits.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables AS TableProperties

INNER JOIN sys.schemas AS sch
	ON sch.schema_id = TableProperties.schema_id

INNER JOIN sys.indexes AS Indices
	ON TableProperties.object_id = Indices.object_id

INNER JOIN sys.partitions AS PartitionProperties
	-- ON PartitionProperties.object_id = TableProperties.object_id
	ON PartitionProperties.object_id = Indices.object_id
	AND Indices.index_id = PartitionProperties.index_id

INNER JOIN sys.allocation_units AS AllocUnits
	ON PartitionProperties.partition_id = AllocUnits.container_id

WHERE (1=1)
AND TableProperties.name NOT LIKE 'dt%'
AND TableProperties.is_ms_shipped = 0
-- AND Indices.OBJECT_ID > 255

GROUP BY
	 sch.name
	,TableProperties.name
	,PartitionProperties.Rows
      -- TableProperties.is_ms_shipped -- Group for entire table

ORDER BY
	 TotalSpaceKB DESC
	,TableName