create procedure sp_db_freespace
	@updateusage char(1) = NULL

/*
**  Single parameter @updateusage will update the usage statistics if set to 'Y'
**  This will require locks on the database, so use when update activity is minimal

**  This SP should be created in master, and called from the database you require
**  information on. e.g.

**  USE <dbname>
**  GO
**  exec sp_db_freespace ['Y']

**  John Thorpe
**  SQL Server MCP, 
**  Director Millennia Computer Services Ltd., UK
**  October 1998
**  Mail to: John@millennia-ltd.co.uk
*/

as

set nocount on
declare @free float,
	@dbsize float,

	@allocated float,
	@percentfree int


/*
**  Calculate summary data for entire db - data segment

*/

IF @updateusage = 'Y'
BEGIN
	DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS

	CHECKPOINT
END

print ' '

select @dbsize = sum(size)
	from master..sysusages
	where dbid = db_id() and segmap != 4

	
select  @allocated = sum(reserved)
	from sysindexes
	where indid in (0, 1, 255) and segment != 2

select @free = (@dbsize-@allocated)

select @percentfree = convert(int,(@free*100/@dbsize))

select  'DATA SIZE' = convert(varchar(10),@dbsize*2/1024) + ' Mb',
	'DATA USE ' = convert(varchar(10),@allocated*2/1024) + ' Mb',
	'SPACE FREE' = convert(varchar(10),@free*2/1024) + ' Mb',
	'PERCENT FREE' = @percentfree

print ' '

/*
**  Calculate summary data for entire db - log segment
*/
if exists (select size
		from master..sysusages
		where dbid = db_id() and segmap = 4)
begin
	IF @updateusage = 'Y'
	BEGIN	

		DBCC CHECKTABLE (syslogs) WITH NO_INFOMSGS
	END

print ' '


	select @dbsize = sum(size)
		from master..sysusages
		where dbid = db_id() and segmap = 4
	
	select  @allocated = sum(reserved)
		from sysindexes
		where indid in (0, 1, 255) and segment = 2
	select @free = (@dbsize-@allocated)

	select @percentfree = convert(int,(@free*100/@dbsize))

select  'LOG SIZE' = convert(varchar(10),@dbsize*2/1024) + ' Mb',
	'LOG USE ' = convert(varchar(10),@allocated*2/1024) + ' Mb',
	'LOG FREE' = convert(varchar(10),@free*2/1024) + ' Mb',
	'PERCENT FREE' = @percentfree

end

set nocount off
GO