(Powershell) Find all text/ntext Columns in Databases and Export Report to a Text File






Import-Module -Name 'SQLPS' -DisableNameChecking
#Assign variables
$Instance   = "MC\MSSQL2014"
$finds=0
 
$report

$Server = New-Object Microsoft.SqlServer.Management.SMO.Server("$Instance")


$dbcnt =$Server.Databases.Count


foreach ($database in $server.databases)
{
Write-host $("{0:00} Databases left to process " -f $dbcnt)

foreach ($table in $database.tables)
{

$hasHeaderPrinted=$false

[string]$tableName = "$($database.Name)\$($table.schema).$($table.Name)"

	if ($tableName.lenth -gt 100)
	{$padDash=2}
	else
	{$padDash=100-$tableName.Length}

foreach($column in $table.Columns)
{ 
if ( $column.Datatype.ToString() -eq "text" -Or $column.Datatype.ToString() -eq "ntext")
{
if($hasHeaderPrinted -eq $false)
{  
$report += "`r`n -- $tableName $("-" * $padDash) `r`n "
$hasHeaderPrinted = $true
}
 $report += " {0:0000}: $tableName.$($column.Name) is $($column.Datatype.ToString()) `r`n"  -f ++$finds
 
}
}
}
$dbcnt--
}


Set-content -Value $report -Path "C:\temp\mySQLReport.txt"
Get-Content "C:\temp\mySQLReport.txt"
notepad "C:\temp\mySQLReport.txt"


 
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