MSSQL: Script to reorg vCenter indexes in SQL Server

Script to reorg vcenter indexes above 70% fragmentations.

SQLCMD -i d:\reorf.sql -o d:\reorg.txt

use vcdb

select 'alter index ' +  ind.name + ' on ' + OBJECT_NAME(ind.OBJECT_ID) + ' reorganize;' from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind  ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id

 WHERE indexstats.avg_fragmentation_in_percent > 70  ORDER BY indexstats.avg_fragmentation_in_percent DESC;

Changed database context to 'VCDB'.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

alter index VPXI_EVENT_EVENT_TYPE on VPX_EVENT reorganize;

alter index VPX_EVENT_ARG_F1 on VPX_EVENT_ARG reorganize;

alter index PK_VPX_DBM_COUNTER_VALUE on VPX_DBM_COUNTER_VALUE reorganize;

alter index VPXI_EVENT_CATEGORY on VPX_EVENT reorganize;

alter index VPXI_EVENT_CREATE_TIME on VPX_EVENT reorganize;

alter index VPXI_EVENT_EXTENDED_CLASS on VPX_EVENT reorganize;

alter index VPX_EVENT_ARG_F2 on VPX_EVENT_ARG reorganize;

alter index PK_VPX_SAMPLE_TIME1 on VPX_SAMPLE_TIME1 reorganize;

alter index PK_VPX_SAMPLE_TIME2 on VPX_SAMPLE_TIME2 reorganize;

alter index PK_VPX_SAMPLE_TIME3 on VPX_SAMPLE_TIME3 reorganize;

alter index VPX_SAMPLE_TIME3_U1 on VPX_SAMPLE_TIME3 reorganize;

alter index PK_VPX_TABLE on VPX_TABLE reorganize;

(12 rows affected)

MSSQL: Transferring ownership from one owner to another in sql server.

Occasionally, this could happen in vCenter where some objects belong to other owners aside from dbo.

Referemce: https://msdn.microsoft.com/en-us/library/ms173423.aspx

Transfer vpx_version from dbo to guest.

alter schema guest transfer dbo.vpx_version;

sp_tables vpx_version

select distinct sys.schemas.name from sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id and sys.schemas.name <>'sys';

Transfer vpx_version from guest to dbo.

alter schema dbo transfer guest.vpx_version;

sp_tables vpx_version

To change all objects from none dbo to dbo schema.

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o. Name

    FROM sys.Objects o

    INNER JOIN sys .Schemas s on o. schema_id = s. schema_id

    WHERE s.Name = 'dbo'

    And (o. Type = 'U' Or o .Type = 'P' Or o.Type = 'V')