Tiek izmantoti divi skati (Catalog Views) informācijas iegūšanai- Sys.Partitions un Sys.Object.
Tiek izvadīts tabulas/skata nosaukums un tam izveidoto indeksi, to daļas, rindu skaits katrā daļā. Lai pievērstu uzmanību gadījumiem, kad tabula/skats ir dalīts, bet kāds indekss nav dalīts un gadījumiem, kad tabula/skats nav dalīts, bet kāds indekss ir dalīts, skripts izvada "Uzmanību!!!" attiecīgajās vietās.
declare @tabName nvarchar(max)
declare @objId int
declare @txt nvarchar(max)
declare @indName nvarchar(max)
declare @type nvarchar(max)
declare @pNr int
declare @pRows int
declare @ind_id int
declare cur cursor for
Select object_name(object_id), object_id
From Sys.Objects
Where object_id In (Select object_id
From Sys.Partitions
Group By object_id, index_id
Having Count(*) > 1)
open cur
fetch next from cur into @tabName, @objId
while @@fetch_status = 0
Begin
print @tabName
-- Info par tabulas indeksiem:
declare curInd cursor for
Select i.index_id, i.Name, i.type_desc, partition_number, rows
From Sys.Indexes i
Left Join Sys.Partitions p on p.object_id = i.object_id and p.index_id = i.index_id
Where i.Object_ID = @objId
Order By i.name, p.partition_number
open curInd
fetch next from curInd into @ind_id, @indName, @type, @pNr, @pRows
while @@fetch_status = 0
Begin
Set @txt = ' '
If(Select Count(*) From Sys.Partitions Where object_id = @objId And index_id = @ind_id) = 1
Set @txt = @txt + 'Uzmanību!!! '
Set @txt = @txt + IsNull(@indName,'Heap') + ' (' + @type + ') Partition Nr: ' + Cast(@pNr as nvarchar(5)) +
' Rows: ' + Cast(@pRows as nvarchar(10))
print @txt
fetch next from curInd into @ind_id, @indName, @type, @pNr, @pRows
End
close curInd
deallocate curInd
print ''
fetch next from cur into @tabName, @objId
End
close cur
deallocate cur
Nav komentāru:
Ierakstīt komentāru