Description
This procedure, when executed, will return the space used for each table within the Ignite repository. Install the stored procedure into the Ignite repository database.
Stored Procedure
declare @IgniteDB varchar(256),
@FullName varchar(256),
@Name varchar(256),
@schema varchar(256)
set @IgniteDB = 'ignite_repository'
if object_id(N'tempdb..[#TableSizes]') is not null drop table #TableSizes ;
if object_id(N'tempdb..[#TableSizesBuffer]') is not null drop table #TableSizesBuffer ;
create table #TableSizes
(
[Schema] nvarchar(128),
[Table Name] nvarchar(128),
[Number of Rows] char(11),
[Reserved Space] varchar(18),
[Data Space] varchar(18),
[Index Size] varchar(18),
[Unused Space] varchar(18)) ;
select * into #TableSizesBuffer from #TableSizes where 1=2
declare curSchemaTable cursor for
select '['+table_schema+']'+ '.'+'['+ table_name+']', table_name, table_schema
from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG = @IgniteDB;
open curSchemaTable ;
fetch curSchemaTable into @FullName,@Name,@schema ;
@FETCH_STATUS = 0 )
begin
truncate table #TableSizesBuffer
insert into #TableSizesBuffer ([Table Name],[Number of Rows],[Reserved Space],[Data Space],[Index Size],[Unused Space] )
exec sp_spaceused @objname = @FullName ;
update #TableSizesBuffer set [Schema]=@schema,
[Reserved Space]=SUBSTRING([Reserved Space], 1, LEN([Reserved Space])-3),
[Data Space]=SUBSTRING([Data Space], 1, LEN([Data Space])-3),
[Index Size]=SUBSTRING([Index Size], 1, LEN([Index Size])-3),
[Unused Space]=SUBSTRING([Unused Space], 1, LEN([Unused Space])-3)
where [Table Name]=@Name
insert into #TableSizes select * from #TableSizesBuffer
fetch curSchemaTable into @FullName,@Name,@schema ;
end
close curSchemaTable ;
deallocate curSchemaTable ;
select d.name [Database Name], d.ID [Ignite ID],
SUM(CONVERT(INT, [Data Space]))/1024 [Total Data MB],
SUM(CONVERT(INT, [Index Size]))/1024 [Total Index MB],
SUM(CONVERT(INT, [Reserved Space]))/1024 [Total Allocated MB],
SUM(CONVERT(INT, [Unused Space]))/1024 [Total Unused MB]
from #TableSizes ts, ignite.COND d
where ts.[Table Name] like 'CON%_'+CONVERT(varchar,d.id)
group by d.name, d.ID
order by [Total Allocated MB] desc
select SUM(CONVERT(INT, [Reserved Space]))/1024 [Total Allocated MB],
SUM(CONVERT(INT, [Unused Space]))/1024 [Total Unused MB]
from #TableSizes
