Page 1 of 1

add property from sql data

Posted: Wed Nov 28, 2018 2:13 am
by Filters
I need to add property to each record when I get result from sql query or add header so I can work with that data later
can some one help me how to do this just i'm new to powershell

Code: Select all

function gettables
{
    $ServerInstance = "****** "
    $Database = "**********"
    $ConnectionTimeout = 30
    $Query = "SELECT TABLE_NAME FROM ****.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';"
    $QueryTimeout = 120
    $conn = new-object System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance, $Database, $ConnectionTimeout
    $conn.ConnectionString = $ConnectionString
    $conn.Open()
    $cmd = new-object system.Data.SqlClient.SqlCommand($Query, $conn)
    $cmd.CommandTimeout = $QueryTimeout
    $ds = New-Object system.Data.DataSet
    $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    return $ds.Tables[0]
}

Re: add property from sql data

Posted: Wed Nov 28, 2018 2:30 am
by jvierra
The results of your query has a header for each column.

Here is the safest and fastest way to get a table.

Code: Select all

function Get-Table{
    param(
        [Parameter(Mandatory)]
        $ServerInstance,
        [Parameter(Mandatory)]
        $Database
    )
    $connStr = 'Server={0};Database={1};Integrated Security=True' -f $ServerInstance, $Database
    $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';"
    $dt = New-Object System.Data.DataTable
    
    Try{
        $conn = new-object System.Data.SqlClient.SQLConnection($connStr)
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $sql
        $rdr = $cmd.ExecuteReader()
        [void]$dt.Load($rdr)
        $conn.Close()
        $dt
    }
    Catch{
        Throw $_
    }
}
Get-Table -ServerInstance ALPHA -Database master

Re: add property from sql data

Posted: Wed Nov 28, 2018 2:39 am
by jvierra
A even better way to do this is this. This will allow you to use any SQL to query any database in any server you have access to.

Code: Select all


function Get-Table{
    param(
        [Parameter(Mandatory)]
        $ServerInstance,
        [Parameter(Mandatory)]
        $Database,
        [Parameter(Mandatory)]
        $Query
    )
    $connStr = 'Server={0};Database={1};Integrated Security=True' -f $ServerInstance, $Database
    $dt = New-Object System.Data.DataTable
    
    Try{
        $conn = new-object System.Data.SqlClient.SQLConnection($connStr)
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $sql
        $rdr = $cmd.ExecuteReader()
        [void]$dt.Load($rdr)
        $conn.Close()
        $dt
    }
    Catch{
        Throw $_
    }
}
$sql = 'SELECT * FROM INFORMATION_SCHEMA.TABLES'
Get-Table -ServerInstance ALPHA -Database master -Query $sql | 
    Where{$_.TABLE_TYPE -eq 'BASE TABLE'}

Re: add property from sql data

Posted: Wed Nov 28, 2018 3:25 am
by Filters
ty for help you saved me a lot of time ;)