There was a requirement to allow non -technical users, to manage archiving
their databases on a SQL Server 2005 platform. They wanted to have the capacity to move the databases from a SAN drive

to SATA drive, but they continued to have a problem regarding space on the destination drive. In other words, having

knowledge of the existing database and comparing to the space available on the destination drive.

The following code satisfied the requirement.

Firstly, create the following procedure (fnSpace_Integrity), which has 2 input parameters.

@db (which is the db name)

@intendedDrive (the targe drive )

Secondly, call the procedure with code similar to the CODE 2 section. You can do whatever you want from the result

-------------------------------------CODE 1 START-------------------------------------
CREATE PROCEDURE [dbo].[fnSpace_Integrity]
@db VARCHAR(200) ,
@intendedDrive char(1),
@spResult int OUTPUT

AS

DECLARE @dbsize bigint,@logsize bigint,
@MB_Free int,
@reservedpages  bigint,
@database_size VARCHAR(100)

--establish db size in MB 
select  @database_size   = convert(
        int,
        case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
        when convert(bigint, sum(s_mf.size)) >= 268435456
        then null
        else (sum(s_mf.size)*8)/1024 -- Convert from 8192 byte pages to Kb then to MB
        end)
from   sys.master_files s_mf
where
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
        and db_name(s_mf.database_id) = @db
    group by s_mf.database_id
    order by 1

PRINT @database_size

create table #FreeSpace(
Drive char(1), 
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
select @MB_Free = MB_Free  from #FreeSpace WHERE drive = @intendedDrive
drop table #FreeSpace

--compare intended drive space with the database size  
if @MB_Free > @database_size 
begin
select  @spResult = 1 
end 
else 
begin
select  @spResult = 0
end 
--------------------------------CODE 1 END-----------------------------------------------------

--------------------------------CODE 2 START------------------------------------------------------
declare @spaceResult INT 
exec fnSpace_Integrity  @DB_NAME_EXISTING,@BackupDrive,@spaceResult OUT
select @spaceResult

if @spaceResult = 0 
begin
   PRINT 'There is not enough space on drive ' + @BackupDrive + ' to move the database : ' +
@DB_NAME_EXISTING
end
------------------------------CODE 2 END----------------------------------------------------------

Republished from http://www.sqlserver-dba.com.