Powershell Primary Key & Clustered Index Check

It’s considered a bad practice  Not using Primary Keys and Clustered Indexes here’s a Powershell script that can make checking a database for this very easy. Just set the $server to the sql instance you want to check and $database as appropriate.

# variables
$server = "sqlinstance";
$database = "badDB";
# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# create server objects
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server $server;
# Get the database
$db = $srv.Databases[$database];
# Get the tables
$tables = $db.Tables;
# process each table
foreach($table in $tables) #HasClusteredIndex
{
	# Check indexes for primary key
	$indexes = $table.Indexes;
	$has_pk = $false;
	foreach($idx in $indexes)
	{
		# I this index a PK?
		if($idx.IndexKeyType -eq "DriPrimaryKey")
		{
			$has_pk = $true;
		}
	}
	# Does the table have a clustered index?
	$has_clustered_idx = $table.HasClusteredIndex;
	if($has_pk -eq $false -and $has_clustered_idx -eq $false)
	{
		Write-Host "$table has no PK or clustered index.";
	}
	elseif($has_pk -eq $false)
	{
		Write-Host "$table has no PK.";
	}
	elseif($has_clustered_idx -eq $false)
	{
		Write-Host "$table has no clustered index.";
	}
}

The script will output something like below;

dbo.table1 has no PK or clustered index.
dbo.table2 has no PK or clustered index.
dbo.table3 has no PK.
dbo.table4 has no PK or clustered index.
dbo.table5 has no PK or clustered index.
dbo.table6 has no clustered index.

Hopefully your list isn’t too long!