Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 5 years and 4 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
Filters
Posts: 5
Last visit: Fri Feb 14, 2020 12:43 am
Post
by Filters » Wed Nov 28, 2018 2:13 am
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]
}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times
Post
by jvierra » Wed Nov 28, 2018 2:30 am
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
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times
Post
by jvierra » Wed Nov 28, 2018 2:39 am
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'}
Filters
Posts: 5
Last visit: Fri Feb 14, 2020 12:43 am
Post
by Filters » Wed Nov 28, 2018 3:25 am
ty for help you saved me a lot of time
This topic is 5 years and 4 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked