Here’s a quick powershell script to list all tables with no primary key index in powershell. The servers.txt requires that you have the instance name, so if the server is a default instance, use SERVERNAME\DEFAULT as the instance name.
function FindTablesNoClustIdx([string]$ServerName)
{
sl SQLSERVER:\SQL\$ServerName\DATABASES
$databases = gci
foreach($database in $databases)
{
$tables = $database.Tables
foreach($table in $tables)
{
$hasPK = $false
foreach($index in $table.Indexes)
{
if($index.IndexKeyType -eq [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey)
{
$hasPK = $true
}
}
if($hasPK -eq $false)
{
Write-Host "Table $Table.Name in database $database.name on server $ServerName has no primary key index"
}
}
}
}
$smo = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
cls
$serverFile = "C:\Test\Servers.txt"
$servers = Get-Content $serverFile
foreach($server in $servers)
{
FindTablesNoClustIdx $server
}
Sql Server Powershell 