Check Disk Space

Checks the disk space



USE  [databasename]
GO

EXEC MASTER..xp_fixeddrives

/***********************************************

************************************************/

SELECT '['+ SCHEMA_NAME(A.schema_id) 
     + '].[' +  A.Name + ']' AS [TABLE]
     , SUM(B.rows) AS [RowCount]
  FROM sys.objects A
  JOIN sys.partitions B ON A.object_id = B.object_id
 WHERE A.type = 'U'
 -- AND SCHEMA_NAME(A.schema_id) = 'OTH'
GROUP BY    A.schema_id, A.Name
ORDER BY 2 desc
GO

/***********************************************

************************************************/

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
     FROM sys.tables t
     JOIN sys.indexes i 
       ON t.OBJECT_ID = i.object_id
     JOIN sys.partitions p 
       ON i.object_id = p.OBJECT_ID 
      AND i.index_id = p.index_id
     JOIN sys.allocation_units a 
       ON p.partition_id = a.container_id
LEFT JOIN sys.schemas s 
       ON t.schema_id = s.schema_id
    WHERE t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 4 DESC, t.Name

GO

/***********************************************

************************************************/

------------------ All Db Sizes
with fs
as
(
    select database_id, [type], size * 8.0 / 1024 size
    from sys.master_files
)
select name,
    (select sum(size) from fs where [type] = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where [type] = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
order by 3 desc ,2 desc