Situatie
Mai jos aveti o procedura ce returneaza top n cele mai mari tabele din baza de date dupa numarul de inregistrari. (unde n este parametru al procedurii, deci se poate alege valoarea dorita)
Solutie
– exec TopTabeleMari_prc 10
alter procedure TopTabeleMari_prc
@n int
as
BEGIN
Select Name into #tableNames
from sysobjects where xtype = ‘U’ order by 1
Create Table #TableCount (TableName Varchar(100), NoOfRowCount bigint)
declare @name varchar(100)
declare curs cursor for select * from #tableNames
open curs
fetch next from curs into @name
while @@fetch_status=0
begin
Insert #TableCount
exec (‘select ”’ + @name + ”’ , count(1) from ‘ + @name)
fetch next from curs into @name
end
close curs
deallocate curs
Select
row_number() over (order by NoOfRowCount desc) as Number, *
into #temp
from #TableCount
order by 3 desc
select * from #temp
where Number <= @n
drop table #tableNames
drop table #TableCount
drop table #temp
END
Leave A Comment?