Here’s a quick script to grab the schema definition for a remote query to see the type of schema of the result set. Handy for when you’re doing OpenQuery to retrieve a resultset into a staging table, but don’t know the specific data types being returned from said remote query.
cls
$sql = "
SELECT * FROM OPENQUERY(LINKEDSERVERNAME, '
SELECT Column1, Column2 FROM RemoteSource
')
"
$serverConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$serverConn.ServerInstance = 'ServerInstance'
$serverConn.DatabaseName = 'master'
$serverConn.LoginSecure = $false;
$serverConn.Login = 'LoginName'
$serverConn.SecurePassword = get-content "c:\SecureCredential.txt" | convertto-securestring
$outputs = @();
$reader = $serverConn.ExecuteReader($sql);
$dt = $reader.GetSchemaTable()
foreach($row in $dt.Rows){
$output = New-Object -TypeName PSObject -Property @{
FieldName = $row.Item("ColumnName")
TypeName = $row.Item("DataTypeName")
Scale = $row.Item("NumericScale")
Precision = $row.Item("NumericPrecision")
Nullable = $row.Item("AllowDBNull")
ProviderType = $row.Item("ProviderSpecificDataType")
}
$outputs += $output
}
$outputs | SELECT FieldName, TypeName, Scale, Precision, Nullable, ProviderType | fl
Sql Server Powershell 
Leave a comment