Collect SQL Server Index Usage Information for all Instances with PowerShell

$server = "xxxx"
$inventoryDB = "xxxx"
 
#Section 1 START
#Create the Monitoring schema if it doesn't exist
$schemaCreationQuery = "
IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'Monitoring') 
EXEC('CREATE SCHEMA Monitoring AUTHORIZATION dbo')
"
Invoke-Sqlcmd -Query $schemaCreationQuery -Database $inventoryDB -ServerInstance $server
   
#Create the central table where you will store the information gathered from all the instances
$indexStatusTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'IndexStatus' AND xtype = 'U')
CREATE TABLE [Monitoring].[IndexStatus](
   [instance] [varchar](255) NULL,
   [database] [varchar](255) NULL,
   [schema] [varchar](255) NULL,
   [table] [varchar](255) NULL,
   [index] [varchar](255) NULL,
   [type] [varchar](255) NULL,
   [allocation_unit_type] [varchar](255) NULL,
   [fragmentation] [decimal](10, 2) NULL,
   [pages] [int] NULL,
   [writes] [int] NULL,
   [reads] [int] NULL,
   [disabled] [tinyint] NULL,
   [stats_timestamp] [datetime] NULL
) ON [PRIMARY]
"
Invoke-Sqlcmd -Query $indexStatusTableCreationQuery -Database $inventoryDB -ServerInstance $server
 
#Clean the Monitoring.IndexStatus table
Invoke-Sqlcmd -Query "TRUNCATE TABLE Monitoring.IndexStatus" -Database $inventoryDB -ServerInstance $server
#Section 1 END
 
#Section 2 START
$spCreationQuery = "
USE ["
 
$spCreationQuery += $inventoryDB
 
$spCreationQuery += "]
GO

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'indexes_status'
          AND SCHEMA_NAME(schema_id) = 'Monitoring'
          AND type = 'P'
       )      
DROP PROC Monitoring.indexes_status
GO
 
-- =============================================
-- Author:      Alejandro Cobar
-- Create date: 2/20/2019
-- Description: Grabs index information status for all databases in an instance
-- =============================================
CREATE PROCEDURE [Monitoring].[indexes_status] 
AS
BEGIN
   SET NOCOUNT ON;
 
   DECLARE @query VARCHAR(MAX);
 
   SET @query = '
   DECLARE @command VARCHAR(MAX)
 
   DECLARE @SDBA_IndexFragmentation TABLE(
      database_name VARCHAR(255),
      Eschema VARCHAR(255),
      xTable VARCHAR(255),
      xIndex VARCHAR(255),
      xType VARCHAR(255),
      xAllocUnitType VARCHAR(255),
      avg_fragmentation_percent DECIMAL(10,2),
      page_count INT,
      writes INT,
      reads INT,
      disabled TINYINT,
      xStatsTimestamp DATETIME
   )
 
   SELECT @command = '+CHAR(39)+'Use [?]
   DECLARE @DB_ID INT;
   SET @DB_ID = DB_ID();
 
   SELECT 
   db_name(@DB_ID) db_Name, 
   s.name AS ESchema,
   t.name AS xTable,
   i.name AS xIndex,
   i.type_desc AS xType,
   ips.alloc_unit_type_desc AS xAllocUnitType,
   CONVERT(DECIMAL(10,2),ips.avg_fragmentation_in_percent) AS fragmentation,
   ips.page_count,
   ISNULL(ius.user_updates,0) AS writes,
   ISNULL(ius.user_seeks + ius.user_scans + ius.user_lookups,0) AS reads,
   i.is_disabled AS disabled, 
   STATS_DATE(st.object_id, st.stats_id)
   FROM sys.indexes i 
   JOIN sys.tables t ON i.object_id = t.object_id 
   JOIN sys.schemas s ON s.schema_id = t.schema_id
   JOIN sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, NULL) ips ON ips.database_id = @DB_ID AND ips.object_id = t.object_id AND ips.index_id = i.index_id
   LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = @DB_ID AND ius.object_id = t.object_id AND ius.index_id = i.index_id
   JOIN sys.stats st ON st.object_id = t.object_id AND st.name = i.name
   WHERE i.index_id > 0'+CHAR(39)+'
  
   INSERT INTO @SDBA_IndexFragmentation
   EXEC sp_MSForEachDB @command
 
   SELECT SERVERPROPERTY(''SERVERNAME'') AS [instance], database_name AS [database], Eschema AS [schema], xTable AS [table], xIndex AS [index], xType AS [type], xAllocUnitType AS [allocation unit type],avg_fragmentation_percent AS [fragmentation], page_count AS [pages], writes, reads , disabled, xStatsTimestamp AS [stats timestamp]
   FROM @SDBA_IndexFragmentation 
   WHERE database_name NOT IN (''msdb'',''master'',''model'',''tempdb'');
   '
 
   SELECT @query AS tsql;
END   
"
Invoke-Sqlcmd -Query $spCreationQuery -Database $inventoryDB -ServerInstance $server
#Section 2 END
 
 
#Section 3 START
#Fetch all the instances from the list you specify
<#
   This is an example of the result set that your query must return
   ###################################################
   # name                     #  instance            #
   ###################################################
   # server1.domain.net,45000 #  server1             #
   # server1.domain.net,45001 #  server1\MSSQLSERVER1# 
   # server2.domain.net,45000 #  server2             #
   # server3.domain.net,45000 #  server3             #
   # server4.domain.net       #  server4\MSSQLSERVER2#
   ###################################################               
#>
 
#Put in your query that returns the list of instances as described in the example result set above
$instanceLookupQuery = "SELECT name, instance FROM instances"
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
#Section 3 END
 
#Section 4 START
$indexStatusQuery = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query "EXEC Monitoring.indexes_status" -MaxCharLength 8000
#Section 4 END
 
#Section 5 START
#For each instance, grab the index information for all the databases
foreach ($instance in $instances){
    Write-Host "Fetching indexes information for instance" $instance.instance
   
   #Go grab the indexes information for all the databases in the instance
   $results = Invoke-Sqlcmd -Query $indexStatusQuery.tsql -ServerInstance $instance.name -ErrorAction Stop
    
   #Perform the INSERT in the IndexStatus table only if it returned at least 1 row
    if($results.Length -ne 0){
        $counter = 0
 
      #Build the insert statement
      $insert = "INSERT INTO Monitoring.IndexStatus VALUES"
      foreach($result in $results){
            $counter++
 
            if($result['instance'].ToString().trim() -eq [String]::Empty){$instance = "''"} else{$instance = $result['instance'] }
            if($result['database'].ToString().trim() -eq [String]::Empty){$database = "''"} else{$database = $result['database'] }
            if($result['schema'].ToString().trim() -eq [String]::Empty){$schema = "''"} else{$schema = $result['schema']}
            if($result['table'].ToString().trim() -eq [String]::Empty){$table = "''"} else{$table = $result['table']}
            if($result['index'].ToString().trim() -eq [String]::Empty){$index = "''"} else{$index = $result['index'] }
            if($result['type'].ToString().trim() -eq [String]::Empty){$type = "''"} else{$type = $result['type']}
            if($result['allocation unit type'].ToString().trim() -eq [String]::Empty){$allocationUnitType = "''"} else{$allocationUnitType = $result['allocation unit type']}
            if($result['fragmentation'].ToString().trim() -eq [String]::Empty){$fragmentation = "''"} else{$fragmentation = $result['fragmentation']}
            if($result['pages'].ToString().trim() -eq [String]::Empty){$pages = "''"} else{$pages = $result['pages'] }
            if($result['writes'].ToString().trim() -eq [String]::Empty){$writes = "''"} else{$writes = $result['writes'] }
            if($result['reads'].ToString().trim() -eq [String]::Empty){$reads = "''"} else{$reads = $result['reads'] }
            if($result['disabled'].ToString().trim() -eq [String]::Empty){$disabled = "''"} else{$disabled = $result['disabled'] }
            if($result['stats timestamp'].ToString().trim() -eq [String]::Empty){$statsTimestamp = "''"} else{$statsTimestamp = $result['stats timestamp']}
                   
         $insert += "
         (
         '"+$instance+"',
         '"+$database+"',
         '"+$schema+"',
         '"+$table+"',
         '"+$index+"',
         '"+$type+"',
         '"+$allocationUnitType+"',
         "+$fragmentation+",
         "+$pages+",
         "+$writes+",
         "+$reads+",
         "+$disabled+",
            '"+$statsTimestamp+"'
         ),
         "
            $insert = $insert -replace "''",'NULL'
            $insert = $insert -replace "NULLNULL",'NULL'
 
            if($counter -eq 1000){
                Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
                $counter = 0
                $insert = "INSERT INTO Monitoring.IndexStatus VALUES" 
            }
 
      } 
 
      #Store the results in the local Monitoring.IndexStatus table in our Lab Server instance
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
   }   
}
#Section 5 END
 
Write-Host "Done!"