Skip to content

October 20, 2011

Finding tables with no Primary Key Index in Powershell

by Scott Newman


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)
	$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 $ on server $ServerName has no primary key index"

$smo = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$serverFile = "C:\Test\Servers.txt"
$servers = Get-Content $serverFile
foreach($server in $servers)
	FindTablesNoClustIdx $server
Read more from Powershell

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: