dfg

 
/****** Object:  StoredProcedure [dbo].[GetDatabaseSize]    Script Date: 12/29/2009 17:00:29 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[GetDatabaseSize]
AS
BEGIN
 
	--tablesizes
	set nocount on;
 
	set transaction isolation level read uncommitted;
 
 
	print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
 
	select
		[FileSizeMB]	=
			convert(numeric(10,2),sum(round(a.size/128.,2))),
			[UsedSpaceMB]	=
			convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
			[UnusedSpaceMB]	=
			convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
		[Type] =
			case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
		[DBFileName]	= isnull(a.name,'*** Total for all files ***')
	from
		sysfiles a
	group by
		groupid,
		a.name
		with rollup
	having
		a.groupid is null or
		a.name is not null
	order by
		case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
		a.groupid,
		case when a.name is null then 99 else 0 end,
		a.name
 
 
 
 
	create table #TABLE_SPACE_WORK
	(
		TABLE_NAME 	sysname		not null ,
		TABLE_ROWS 	numeric(18,0)	not null ,
		RESERVED 	varchar(50) 	not null ,
		DATA 		varchar(50) 	not null ,
		INDEX_SIZE 	varchar(50) 	not null ,
		UNUSED 		varchar(50) 	not null ,
	)
 
	create table #TABLE_SPACE_USED
	(
		Seq		int		not null	
		identity(1,1)	primary key clustered,
		TABLE_NAME 	sysname		not null ,
		TABLE_ROWS 	numeric(18,0)	not null ,
		RESERVED 	varchar(50) 	not null ,
		DATA 		varchar(50) 	not null ,
		INDEX_SIZE 	varchar(50) 	not null ,
		UNUSED 		varchar(50) 	not null ,
	)
 
	create table #TABLE_SPACE
	(
		Seq		int		not null
		identity(1,1)	primary key clustered,
		TABLE_NAME 	SYSNAME 	not null ,
		TABLE_ROWS 	int	 	not null ,
		RESERVED 	int	 	not null ,
		DATA 		int	 	not null ,
		INDEX_SIZE 	int	 	not null ,
		UNUSED 		int	 	not null ,
		USED_MB				numeric(18,4)	not null,
		USED_GB				numeric(18,4)	not null,
		AVERAGE_BYTES_PER_ROW		numeric(18,5)	null,
		AVERAGE_DATA_BYTES_PER_ROW	numeric(18,5)	null,
		AVERAGE_INDEX_BYTES_PER_ROW	numeric(18,5)	null,
		AVERAGE_UNUSED_BYTES_PER_ROW	numeric(18,5)	null,
	)
 
	declare @fetch_status int
 
	declare @proc 	varchar(200)
	select	@proc	= rtrim(db_name())+'.dbo.sp_spaceused'
 
	declare Cur_Cursor cursor local
	for
	select
		TABLE_NAME	= 
		rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
	from
		INFORMATION_SCHEMA.TABLES 
	where
		TABLE_TYPE	= 'BASE TABLE'
	order by
		1
 
	open Cur_Cursor
 
	declare @TABLE_NAME 	varchar(200)
 
	select @fetch_status = 0
 
	while @fetch_status = 0
		begin
 
		fetch next from Cur_Cursor
		into
			@TABLE_NAME
 
		select @fetch_status = @@fetch_status
 
		if @fetch_status <> 0
			begin
			continue
			end
 
		truncate table #TABLE_SPACE_WORK
 
		insert into #TABLE_SPACE_WORK
			(
			TABLE_NAME,
			TABLE_ROWS,
			RESERVED,
			DATA,
			INDEX_SIZE,
			UNUSED
			)
		exec @proc @objname = 
			@TABLE_NAME ,@updateusage = 'true'
 
 
		-- Needed to work with SQL 7
		update #TABLE_SPACE_WORK
		set
			TABLE_NAME = @TABLE_NAME
 
		insert into #TABLE_SPACE_USED
			(
			TABLE_NAME,
			TABLE_ROWS,
			RESERVED,
			DATA,
			INDEX_SIZE,
			UNUSED
			)
		select
			TABLE_NAME,
			TABLE_ROWS,
			RESERVED,
			DATA,
			INDEX_SIZE,
			UNUSED
		from
			#TABLE_SPACE_WORK
 
		end 	--While end
 
	close Cur_Cursor
 
	deallocate Cur_Cursor
 
	insert into #TABLE_SPACE
		(
		TABLE_NAME,
		TABLE_ROWS,
		RESERVED,
		DATA,
		INDEX_SIZE,
		UNUSED,
		USED_MB,
		USED_GB,
		AVERAGE_BYTES_PER_ROW,
		AVERAGE_DATA_BYTES_PER_ROW,
		AVERAGE_INDEX_BYTES_PER_ROW,
		AVERAGE_UNUSED_BYTES_PER_ROW
 
		)
	select
		TABLE_NAME,
		TABLE_ROWS,
		RESERVED,
		DATA,
		INDEX_SIZE,
		UNUSED,
		USED_MB			=
			round(convert(numeric(25,10),RESERVED)/
			convert(numeric(25,10),1024),4),
		USED_GB			=
			round(convert(numeric(25,10),RESERVED)/
			convert(numeric(25,10),1024*1024),4),
		AVERAGE_BYTES_PER_ROW	=
			case
			when TABLE_ROWS <> 0
			then round(
			(1024.000000*convert(numeric(25,10),RESERVED))/
			convert(numeric(25,10),TABLE_ROWS),5)
			else null
			end,
		AVERAGE_DATA_BYTES_PER_ROW	=
			case
			when TABLE_ROWS <> 0
			then round(
			(1024.000000*convert(numeric(25,10),DATA))/
			convert(numeric(25,10),TABLE_ROWS),5)
			else null
			end,
		AVERAGE_INDEX_BYTES_PER_ROW	=
			case
			when TABLE_ROWS <> 0
			then round(
			(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
			convert(numeric(25,10),TABLE_ROWS),5)
			else null
			end,
		AVERAGE_UNUSED_BYTES_PER_ROW	=
			case
			when TABLE_ROWS <> 0
			then round(
			(1024.000000*convert(numeric(25,10),UNUSED))/
			convert(numeric(25,10),TABLE_ROWS),5)
			else null
			end
	from
		(
		select
			TABLE_NAME,
			TABLE_ROWS,
			RESERVED	= 
			convert(int,rtrim(replace(RESERVED,'KB',''))),
			DATA		= 
			convert(int,rtrim(replace(DATA,'KB',''))),
			INDEX_SIZE	= 
			convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
			UNUSED		= 
			convert(int,rtrim(replace(UNUSED,'KB','')))
		from
			#TABLE_SPACE_USED aa
		) a
	order by
		TABLE_NAME
 
	print 'Show results in descending order by size in MB'
 
	select * from #TABLE_SPACE order by TABLE_NAME asc;
 
	drop table #TABLE_SPACE_WORK
	drop table #TABLE_SPACE_USED 
	drop table #TABLE_SPACE
 
END
 
GO