Running Paul’s nonclustered index count survey against multiple instances

Paul put up a new survey today titled Survey: nonclustered index counts (code to run).  If you’d like to run the query for this survey against multiple SQL Servers at once using PowerShell here is the script to do it:

# Load the Snapins for SqlServer if they aren't currently loaded 
if((Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null)  
{    Add-PSSnapin SqlServerCmdletSnapin100    }

if((Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)  
{      Add-PSSnapin SqlServerProviderSnapin100    }


$Query = "IF EXISTS (SELECT * FROM msdb.sys.objects WHERE [name] = 'SQLskillsPaulsIndexCounts')
    DROP TABLE msdb.dbo.SQLskillsPaulsIndexCounts;
GO
CREATE TABLE msdb.dbo.SQLskillsPaulsIndexCounts (
    BaseType CHAR (10),
    IndexCount SMALLINT);
GO 

EXEC sp_MSforeachdb 
    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT [name] 
    FROM sys.databases WHERE [state_desc] = ''ONLINE''
        AND [database_id] > 4
        AND [name] != ''pubs''
        AND [name] != ''Northwind''
        AND [name] != ''distribution''
        AND [name] NOT LIKE ''ReportServer%''
        AND [name] NOT LIKE ''Adventure%'') AS names WHERE [name] = ''?'')
BEGIN
USE [?]
INSERT INTO msdb.dbo.SQLskillsPaulsIndexCounts
SELECT ''Heap'', COUNT (*)-1
FROM sys.objects o
JOIN sys.indexes i
    ON o.[object_id] = i.[object_id]
WHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
    AND o.[is_ms_shipped] = 0
    AND EXISTS (
        SELECT *
        FROM sys.indexes
        WHERE [index_id] = 0
            AND [object_id] = o.[object_id])
GROUP BY i.[object_id];

INSERT INTO msdb.dbo.SQLskillsPaulsIndexCounts
SELECT ''Clustered'', COUNT (*)-1
FROM sys.objects o
JOIN sys.indexes i
    ON o.[object_id] = i.[object_id]
WHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
    AND o.[is_ms_shipped] = 0
    AND EXISTS (
        SELECT *
        FROM sys.indexes
        WHERE [index_id] = 1
            AND [object_id] = o.[object_id])
GROUP BY i.[object_id];
END';
GO 

SELECT DISTINCT [BaseType], [IndexCount] AS [NCIndexes], COUNT (*) AS [TableCount]
FROM msdb.dbo.SQLskillsPaulsIndexCounts
GROUP BY [BaseType], [IndexCount]
ORDER BY [BaseType], [IndexCount];
GO 

DROP TABLE msdb.dbo.SQLskillsPaulsIndexCounts;
GO"

# To use a text file list of servers, place each server name on a separate line in the file
# then uncomment the following line to read the list from the text file and comment out the
# listed declaration for the servers

#$servers = get-content "serverlist.txt"

# To specify a list of servers, place each server name in double quotes separated by a comma
# multiple lines are allowed.
$ServerList = "AlwaysOnLab1", "AlwaysOnLab2", "AlwaysOnLab3"

foreach ($CurrentServer in $ServerList) 
{ 
    
    Invoke-Sqlcmd -Server $CurrentServer -Database "master" -Query $Query 
}

There are two different ways to feed the server list into the above script, either as a file or as an array of comma separated server names.

Enjoy and don’t forget to post your results back on Paul’s original post as a comment.