SQL Server – aktualizácia štatistík a prebudovanie indexov nad vybranými tabuľkami v procedúre

Procedúra pre SQL Server, ktorá prebuduje indexy a tiež aktualizuje štatistiky nad tabuľkami.

V príklade je prebudovanie indexov zakomentované, pretože môže blokovať prácu používateľov.
Aktualizácia štatistík je v našom prípade zámerne nastavená iba pre A a D tabuľky + sde states tabuľky.

Zdroj: support.esri.com/en/technical-article/000006051

use LPISDB

— Rebuild indexes on tables for all owners in the table registry
— ***************************************************************
—  Run under the DB that holds the SDE Schema tables.
—  If the sde schema is owned by dbo then you will need to change:
—  select distinct owner from sde.sde_table_registry
—  to
—  select distinct owner from dbo.sde_table_registry

Declare @OwnerName varchar(50)
Declare @TableName varchar(50)
Declare @QualifiedName varchar(100)

Declare OwnerCursor cursor for
select distinct owner from sde.SDE_table_registry
open OwnerCursor

Fetch OwnerCursor into @OwnerName
while (@@fetch_status=0)
begin

Declare TableCursor cursor for
— Get all the tables for this user
select so.name from sysobjects so join sysusers su on so.uid = su.uid  where type = ‘U’ and (so.name like ‘A[0-9]%’ or so.name like ‘D[0-9]%’ or so.name in (‘sde_states’,’sde_state_lineages’)) and su.name = @OwnerName
open TableCursor
Fetch TableCursor into @TableName
while (@@fetch_status=0)
 begin
  set @QualifiedName = @OwnerName + ‘.’ + @TableName
  –print ‘Rebilding indexes on  : ‘ + @QualifiedName
  –dbcc dbreindex(@QualifiedName) WITH NO_INFOMSGS
  print ‘Updating Statistics on: ‘ + @QualifiedName
  EXEC (‘UPDATE STATISTICS ‘ + @QualifiedName )
  print ”
  Fetch TableCursor into @TableName
end
Fetch OwnerCursor into @OwnerName
close TableCursor
Deallocate TableCursor
end
close OwnerCursor
Deallocate OwnerCursor

go

IG