CV lai apskatītu dalītos (partitioned) objektus

Izveidoju skriptu, kas izvada visus dalītos (partitioned) datu bāzes objektus- tabulas, skatus un attiecīgos indeksus. Tabula vai materializēts skats var arī nebūt dalīts, bet kāds indekss uz tā vienalga var būt dalīts.

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