Query to get size of all the databases in SQL Server


Script to get size of all the databases in SQL Server Instance

/*– SQL Server 2000 only.
— Display the Database ID, Database Name, Logical File Name,
— MB Size on Disk, GB Size on Disk and Physical File Name
— for all databases in this instance.

*/
use master;
go

select
db.[dbid] as ‘DB ID’
,db.[name] as ‘Database Name’
,af.[name] as ‘Logical Name’
–,af.[size] as ‘File Size (in 8-kilobyte (KB) pages)’
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as ‘File Size (MB)’
,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as ‘File Size (GB)’
,af.[filename] as ‘Physical Name’
from sysdatabases db
inner join sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1,2);

/*– SQL Server 2005 only.
— Display the Database ID, Database Name, Logical File Name,
— MB Size on Disk, GB Size on Disk and Physical File Name
— for all databases in this instance.

*/
use master;
go

select
db.[dbid] as ‘DB ID’
,db.[name] as ‘Database Name’
,af.[name] as ‘Logical Name’
–,af.[size] as ‘File Size (in 8-kilobyte (KB) pages)’
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as ‘File Size (MB)’
,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as ‘File Size (GB)’
,af.[filename] as ‘Physical Name’
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1,2);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: