two/three utility scripts for counting and sizing databases and tables.

richardcbeck

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