Finding tables with no Primary Key Index in Powershell

 

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
}
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: