I was thinking to prepare this script and finally I did. This script will give database size and free space details.
SQL 2005 and 2008
create table #DBSpaceDetails (DBName nvarchar(150), Name nvarchar(100), PhysicalName nvarchar(1000), TotalSize bigint, FreeSpace bigint)
insert into #DBSpaceDetails (DBName, Name, PhysicalName, TotalSize, FreeSpace)
exec sp_msforeachdb 'use ?; SELECT db_name(), name, physical_name, size/128 AS ''TS (MB)'', size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 AS ''AS (MB)'' FROM sys.database_files'
select * from #DBSpaceDetails
Drop table #DBSpaceDetails
Note: size (8 KB page) is unit number and we need to convert this value to actual MB.
1024 KB = 1 MB
1 Unit = 8 KB
10 Unit = 1280 MB (ie 10 * 128 = 1280 MB; 1024/8 =128)
Script for SQL 2000
create table #DBSpaceDetails (DBName nvarchar(150), Name nvarchar(100), PhysicalName nvarchar(1000), TotalSize bigint, FreeSpace bigint)
insert into #DBSpaceDetails (DBName, Name, PhysicalName, TotalSize, FreeSpace)
exec sp_msforeachdb 'use ?; SELECT db_name(), name, filename, size/128 AS ''TS (MB)'', size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 AS ''AS (MB)'' FROM sysfiles'
select * from #DBSpaceDetails
Drop table #DBSpaceDetails
Output Result
