Change Column Length via Powershell

Changing the length of a column is not as straightforward as you would think. There is no .Length property on the smo column object itself, rather you have to set the column.property[‘Length’].value to whatever length you are setting.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$servers = Get-Content c:\Servers.txt

try{
	$servers | %{
		$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
		$srvConn.ServerInstance = $_
		$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
		
		$srv.databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
			$dbName = $_.Name
			$_.Tables | where{$_.Name -eq "TableName" -and $_.Schema -eq "dbo"} | %{
				$tableName = $_.Name
				$_.Columns | where{$_.Name -eq 'ColumnName'} | %{
					if([int]$_.Properties['Length'].value -eq 100){
						$_.Properties['Length'].value = 500
						$_.Alter();
					}
				}
			}
		}
		
	}
}
catch{
	$_ | fl -Force
}

Find database last access date via powershell

Bit of misnomer, it’s still using SQL executed via the dm_db_index_usage_stats dmv to get the last user seek\scan\lookup\update column to determine the last access time, but still a good script to get a list of the last usage dates for all your databases on all your servers. Also includes the last server restart time, as the dm_db_index_usage_stats resets upon server restart. Use at your own risk.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$servers = Get-Content c:\Servers.txt

$outputs = @();

$lastAccessSQL = "
SELECT 
	DB_NAME(database_id) AS DatabaseName,
	COALESCE(
		MAX(last_user_seek),
		MAX(last_user_scan),
		MAX(last_user_lookup),
		MAX(last_user_update),
		'1/1/1900') AS LastAccessDate,
		(SELECT create_date FROM sys.databases WHERE name = 'tempdb') AS LastServerRestart
FROM
	sys.dm_db_index_usage_stats
WHERE DB_NAME(database_id) NOT IN('tempdb', 'master', 'msdb')
GROUP BY database_id
ORDER BY DatabaseName
"

$servers | %{
	$srvName = $_
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $srvName

	$dr = $srvConn.ExecuteReader($lastAccessSQL)

	while($dr.Read()){
		$output = New-Object -TypeName PSObject -Property @{
		    ServerName = $srvName
		    DatabaseName = $dr.GetString(0)
			LastAccessDate = $dr.GetDateTime(1)
			ServerLastRestart = $dr.GetDateTime(2)
		}
		$outputs += $output
	}
	
}
$outputs | SELECT ServerName, DatabaseName, LastAccessDate, ServerLastRestart | Out-GridView

Add a column to a table via powershell

Quick post on how to add a column to a table via powershell. I had a need to blast a new column onto a bunch of databases on a bunch of servers. Powershell makes this too easy. Use at your own risk.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$servers = Get-Content c:\Servers.txt
$tableName = 'TableName'
$schema = 'dbo'
$columnName = 'ColumnName'

try{

	$servers | %{
		$srvName = $_
		$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
		$srvConn.ServerInstance = $srvName
		$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

		$srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
			$dbName = $_.Name
			$table = $_.Tables | where{$_.Name -eq $tableName -and $_.Schema -eq $schema}
			if($table -ne $null){
				$column = $table.Columns | where{$_.Name -eq $columnName}
				if($column -eq $null){  #add the column if it does not exist
					$column = New-Object Microsoft.SqlServer.Management.Smo.Column
					$column.Parent = $table
					$column.Name = $columnName
					$column.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::DateTime
					$column.Create();
				}
			}
		}

	}

}
catch{
	$_ | fl -Force
}

Get the last backup date for all databases using powershell

Quick script to get the last backup date for all databases (excluding tempdb & offline databases). Marginally tested (works on my machine), so use at your own risk.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$servers = Get-Content c:\Servers.txt

$outputs = @();

$servers | %{
	$srvName = $_
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $srvName
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

	$srv.Databases | where{$_.Name -ne 'tempdb' -and $_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{

			$output = New-Object -TypeName PSObject -Property @{
			    Server = $srvName
			    Database = $_.Name
				LastBackupDate = $_.LastBackupDate
			}
			$outputs += $output
		}

}

$outputs | SELECT Server, Database, LastBackupDate