we know that we can use sql query window to get values from Database like "select * from....". Is there any way to get a value through powershell way. I found the way to database table itself but not to the values..
Ex:
Set-location SQLserver:\sql\default\localhost\databases\database\tablesGet-childitem
Above command gives me tables in the DB , but how can i get the values from it.
The SQL Server Powershell provider uses SMO to expose the database object hierarchy. Once you get a SMO child item, you can invoke the corresponding SMO methods on the object.
The SMO Database ExecuteQueryWithResults method can be used to execute a query in the context of a given database. Get the desired database item and invoke this method to return a DataSet object containing the results. Then retrieve the data as desired from the DataSet.
Below is an example gleaned from the SMO reference ((https://msdn.microsoft.com/en-us/library/ms205775.aspx) that can be run from the context of the Databases node:
$db = Get-Item SomeDatabase
$dt = $db.ExecuteWithResults("SELECT * FROM sys.objects;")
$dt.Tables[0] | Format-Table
You can use this
$connectionString = “Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = “SELECT * FROM Tab”
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
Most concise and straightforward option is Invoke-SqlCommand
$results = Invoke-Sqlcmd -ServerInstance "mySqlServer" -Database "myDatabase" -Query "SELECT * FROM MyTable"
Do you means the powershell command like below:
$MyVar = New-Object Microsoft.SqlServer.Management.SMO.Table
$MyVar | Get-Member -Type Properties
For more information you could visit https://technet.microsoft.com/en-us/library/cc281939(v=sql.105).aspx
If you want to get the value of table,
//Connect to the local, default instance of SQL Server.
{
Server srv = default(Server);
srv = new Server();
//Call the EnumCollations method and return collation information to DataTable variable.
DataTable d = default(DataTable);
//Select the returned data into an array of DataRow.
d = srv.EnumCollations;
//Iterate through the rows and display collation details for the instance of SQL Server.
DataRow r = default(DataRow);
DataColumn c = default(DataColumn);
foreach ( r in d.Rows) {
Console.WriteLine("=====================================");
foreach ( c in r.Table.Columns) {
Console.WriteLine(c.ColumnName + " = " + r(c).ToString);
}
}
}
The above is how to do the job via C# but how to do that in powershell: You have got $MyVar values, and you run "$MyVar |Get-Member" is to show all method of all method available for $MyVar, I believe there should have some method allow you iterator all row or column for thr table. The powerShell even can invoke C# method, but this is the last choose.
There is now an sqlserver module available for powershell
You now have available the following two additional commands and options
Read-SqlTableData [[-ServerInstance] <String[]> ] [-ColumnName <String[]> ] [-ColumnOrder <String[]> ] [-ColumnOrderType <OrderType[]> ] [-ConnectionTimeout <Int32> ] [-Credential <PSCredential> ] [-DatabaseName <String> ] [-IgnoreProviderContext] [-OutputAs <OutputTypeSingleTable> {DataSet | DataTable | DataRows} ] [-SchemaName <String> ] [-SuppressProviderContextWarning] [-TableName <String> ] [-TopN <Int64> ] [ <CommonParameters>]
Write-SqlTableData [[-ServerInstance] <String[]> ] -InputData <PSObject> [-ConnectionTimeout <Int32> ] [-Credential <PSCredential> ] [-DatabaseName <String> ] [-Force] [-IgnoreProviderContext] [-Passthru] [-SchemaName <String> ] [-SuppressProviderContextWarning] [-TableName <String> ] [-Timeout <Int32> ] [ <CommonParameters>
