Situatie
Pentru cazurile in care avem nevoie sa aflam care sunt tabelele cu cea mai mare dimensiune dintr-o baza am facut o procedura care returneaza acest top in functie de un parametru, @rows.
Daca @rows = 1 atunci se returneaza top 10 in functie de numarul de linii din tabela.
Daca @rows = 0 atunci se returneaza top 10 in functie de dimensiunea (exprimata in kb) tabelelor.
Solutie
create procedure [dbo].[BD_TopDimensiuniTabele]
@rows bit
as
DECLARE @nume varchar(100)
DECLARE @sql varchar (100)
begin
declare @table2 table(nume varchar(50), rows varchar(50), reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50))
declare x cursor
for
select name from sysobjects (NOLOCK) where xtype=’U’
open x
fetch next from x into @nume
while @@fetch_status=0
begin
set @sql = ‘exec sp_spaceused ”’+@nume+””
insert into @table2
exec (@sql)
fetch next from x into @nume
end
create table #table2 (nume varchar(50), rows bigint, reserved int, data bigint, index_size int, unused int)
insert into #table2
select
nume
,convert(bigint, rows)
,convert(int, substring(reserved,1,charindex(‘ ‘,reserved)-1))
,convert(bigint, substring(data,1,charindex(‘ ‘,data)-1))
,convert(int, substring(index_size,1,charindex(‘ ‘,index_size)-1))
,convert(int, substring(unused,1,charindex(‘ ‘,unused)-1))
from @table2
–insert into PerfDB..BD_Tabele_Mari ( nume, rows, reserved, data, index_size ,unused )
close x
deallocate x
if @rows = 1
select top 10 * from #table2 order by rows desc
else
select top 10 * from #table2 order by data desc
end
Leave A Comment?