(Powershell) Find all text/ntext columns in Databases



Import-Module -Name 'SQLPS' -DisableNameChecking
#Assign variables
$Instance   = "MC\MSSQL2014"
$match=0
 $start = get-date
$Server = New-Object Microsoft.SqlServer.Management.SMO.Server("$Instance")

foreach ($database in $server.databases)
{
foreach ($table in $database.tables)
{
$tableName="$($database.Name)\$($table.schema).$($table.Name)"
foreach($column in $table.Columns)
{ 
if ( $column.Datatype.ToString() -eq "text" -Or $column.Datatype.ToString() -eq "ntext")
{
"$tablename.$($column.Name) is a $($column.Datatype.ToString())"
$match++
}

}
}

}


$end=get-date
$elapsed=$end-$start
"Found $($match)  and elapsed time $($elapsed.TotalSeconds) seconds or $($elapsed.Totalmilliseconds) ms "


 
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