OpenQuery Result Set Data Type

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.

$sql = "
	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

Leave a Reply

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

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.