SHARE
TWEET

Untitled

a guest Mar 24th, 2019 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- TRUNCATE TABLE interface.T_Log;
  2. -- TRUNCATE TABLE T_COR_Log;
  3. -- TRUNCATE TABLE T_LOG_ProzedurRun;
  4. -- TRUNCATE TABLE T_RPT_LOG_ProzedurRun;
  5. -- TRUNCATE TABLE T_COR_Error;
  6. -- TRUNCATE TABLE T_SessionValues;
  7. -- TRUNCATE TABLE VWSsessions;
  8.  
  9. -- sp_helptext 'sp_spaceused';
  10. -- sp_spaceused 'T_SYS_Geschossrechte';
  11.  
  12.  
  13. SELECT
  14.      sch.name
  15.     ,TableProperties.name AS TableName
  16.     ,PartitionProperties.rows AS RowCounts
  17.     ,SUM(AllocUnits.total_pages) * 8 AS TotalSpaceKB
  18.     ,FORMAT( SUM(AllocUnits.total_pages) * 8 ,'N0', 'de-CH') + ' kB' AS TotalSpaceKB_Swiss
  19.     ,SUM(AllocUnits.used_pages) * 8 AS UsedSpaceKB
  20.     ,(SUM(AllocUnits.total_pages) - SUM(AllocUnits.used_pages)) * 8 AS UnusedSpaceKB
  21. FROM sys.tables AS TableProperties
  22.  
  23. INNER JOIN sys.schemas AS sch
  24.     ON sch.schema_id = TableProperties.schema_id
  25.  
  26. INNER JOIN sys.indexes AS Indices
  27.     ON TableProperties.object_id = Indices.object_id
  28.    
  29. INNER JOIN sys.partitions AS PartitionProperties
  30.     -- ON PartitionProperties.object_id = TableProperties.object_id
  31.     ON PartitionProperties.object_id = Indices.object_id
  32.     AND Indices.index_id = PartitionProperties.index_id
  33.    
  34. INNER JOIN sys.allocation_units AS AllocUnits
  35.     ON PartitionProperties.partition_id = AllocUnits.container_id
  36.    
  37. WHERE (1=1)
  38. AND TableProperties.name NOT LIKE 'dt%'
  39. AND TableProperties.is_ms_shipped = 0
  40. -- AND Indices.OBJECT_ID > 255
  41.    
  42. GROUP BY
  43.      sch.name
  44.     ,TableProperties.name
  45.     ,PartitionProperties.Rows
  46.       -- TableProperties.is_ms_shipped -- Group for entire table
  47.    
  48. ORDER BY
  49.      TotalSpaceKB DESC
  50.     ,TableName
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top