From SQL and ME, a SSMS script to determine how large each database is on a SQL Server instance (and get a list of all databases) by name, type (data, log, null) and size (MB). The size MIGHT be the creation size and not necessarily reflect the data in the file.
SELECT [Database Name] = DB_NAME(database_id), [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)' WHEN Type_Desc = 'LOG' THEN 'Log File(s)' ELSE Type_Desc END, [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) FROM sys.master_files -- Uncomment if you need to query for a particular database -- WHERE database_id = DB_ID(‘Database Name’) GROUP BY GROUPING SETS ( (DB_NAME(database_id), Type_Desc), (DB_NAME(database_id)) ) ORDER BY DB_NAME(database_id), Type_Desc DESC GO
This query will get the tables in a single database, from symantec.com as adapted from the Database journal:
use adventureworks; select a2.name [Table], replace(convert (nvarchar, convert(money, a1.rows), 1), '.00','') [Row Count], cast (a1.reserved/128.0/1024 as decimal(9,3)) [Reserved GB], case when a1.reserved = 0 then 0 else cast ((100-(100.0*a1.used/a1.reserved)) as decimal(9,2)) end [Unused %] from ( select ps.object_id, sum (cast (ps.row_count as bigint)) rows, sum (cast (ps.reserved_page_count as bigint)) reserved, sum (cast (ps.used_page_count as bigint)) used from sys.dm_db_partition_stats ps inner join sys.all_objects a2 on a2.object_id = ps.object_id and a2.type IN ('U') group by ps.object_id ) a1 left join sys.all_objects a2 ON a1.object_id = a2.object_id where (a1.reserved/128) > 5 ORDER BY a1.rows DESC, a2.name
the good news is that you get free space, the bad news is that you get it only for each database as you enumerate it.
To get all databases and their size, it can be as simple as this
SELECT d.name, ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 -- Skip system databases GROUP BY d.name ORDER BY d.name
Advertisements