Invoke-SqlQuery.ps1
|
#requires -version 5.0 <# .SYNOPSIS Invoke any T-SQL query against any SQL supported database. .DESCRIPTION This command uses the .NET SqlClient classes to connect to a SQL database and execute any type of query. The default behavior is to use integrated Windows authentication, but you can pass a credential object for a username and password. This works well when run on Linux. When you run a SELECT query the command will write a custom object for each row to the pipeline. .PARAMETER Query Any T-SQL query string .PARAMETER Database The database name. It is assumed the account you are using has the necessary permissions. .PARAMETER ServerInstance The name of the SQL server instance. .PARAMETER Credential If you need to specify a username and password, use a PSCredential object. .EXAMPLE PS C:\Scripts> .\Invoke-SqlQuery "Select * from diskinfo" -Database scripting ComputerName : YPJH10 DiskSize : 206266429440 DriveType : 3 FreeSpace : 27722903552 DriveID : C: DateAdded : 6/23/2017 5:24:01 PM ComputerName : YPJH10 DiskSize : 26843541504 DriveType : 3 FreeSpace : 16025034752 DriveID : D: DateAdded : 6/23/2017 5:24:01 PM This queries the Scripting database in the localhost\SQLExpress instance. .EXAMPLE PS C:\Scripts> .\Invoke-SqlQuery "Insert Into diskinfo (ComputerName,DiskSize,DriveType,FreeSpace,DriveID,DateAdded) Values ('SERVER01',53687091200,3,41875931136,'C:','7/20/2017 4:12PM')" -Database scripting -credential jane Running an INSERT query with alternate credentials. In this situation the user would be prompted for the password. .EXAMPLE PS C:\Scripts> .\Invoke-SqlQuery "Select * from diskinfo where computername='server01'" -database scripting | format-table ComputerName DiskSize DriveType FreeSpace DriveID DateAdded ------------ -------- --------- --------- ------- --------- SERVER01 53687091200 3 46707769344 C: 7/14/2017 4:02:00 PM SERVER01 53687091200 3 41875931136 C: 7/20/2017 4:12:00 PM SERVER01 53687091200 3 41811131136 C: 6/2 0/2017 5:12:00 PM .INPUTS None .OUTPUTS [PSCustomObject] .NOTES Version 1.0 This function does not rely on any SQL modules. Learn more about PowerShell: http://jdhitsolutions.com/blog/essential-powershell-resources/ **************************************************************** * DO NOT USE IN A PRODUCTION ENVIRONMENT UNTIL YOU HAVE TESTED * * THOROUGHLY IN A LAB ENVIRONMENT. USE AT YOUR OWN RISK. IF * * YOU DO NOT UNDERSTAND WHAT THIS SCRIPT DOES OR HOW IT WORKS, * * DO NOT USE IT OUTSIDE OF A SECURE, TEST SETTING. * **************************************************************** #> [cmdletbinding(SupportsShouldProcess,DefaultParameterSetName="Default")] Param( [Parameter(Position = 0, Mandatory, HelpMessage = "The T-SQL query to execute")] [ValidateNotNullorEmpty()] [string]$Query, [Parameter(Mandatory, HelpMessage = "The name of the database")] [ValidateNotNullorEmpty()] [string]$Database, [Parameter(Mandatory,ParameterSetName='credential')] [pscredential]$Credential, #The server instance name [ValidateNotNullorEmpty()] [string]$ServerInstance = "$env:computername\SqlExpress" ) Begin { Write-Verbose "[BEGIN ] Starting: $($MyInvocation.Mycommand)" if ($PSCmdlet.ParameterSetName -eq 'credential') { $username = $Credential.UserName $password = $Credential.GetNetworkCredential().Password } Write-Verbose "[BEGIN ] Creating the SQL Connection object" $connection = New-Object system.data.sqlclient.sqlconnection Write-Verbose "[BEGIN ] Creating the SQL Command object" $cmd = New-Object system.Data.SqlClient.SqlCommand } #begin Process { Write-Verbose "[PROCESS] Opening the connection to $ServerInstance" Write-Verbose "[PROCESS] Using database $Database" if ($Username -AND $password) { Write-Verbose "[PROCESS] Using credential" $connection.connectionstring = "Data Source=$ServerInstance;Initial Catalog=$Database;User ID=$Username;Password=$Password;" } else { Write-Verbose "[PROCESS] Using Windows authentication" $connection.connectionstring = "Data Source=$ServerInstance;Initial Catalog=$Database;Integrated Security=SSPI;" } $connection.open() #join the connection to the command object $cmd.connection = $connection $cmd.CommandText = $query Write-Verbose "[PROCESS] Invoking $query" if ($PSCmdlet.ShouldProcess($Query)) { #determine what method to invoke based on the query Switch -regex ($query) { "^Select (\w+|\*)|(@@\w+ AS)" { $reader = $cmd.executereader() $out=@() #convert datarows to a custom object while ($reader.read()) { $h = [ordered]@{} for ($i=0;$i -lt $reader.FieldCount;$i++) { $col = $reader.getname($i) $h.add($col,$reader.getvalue($i)) } #for $out+=new-object -TypeName psobject -Property $h } #while $out $reader.close() Break } "@@" { $cmd.ExecuteScalar() Break } Default { $cmd.ExecuteNonQuery() | Out-Null } } } #should process } End { Write-Verbose "[END ] Closing the connection" $connection.close() Write-Verbose "[END ] Ending: $($MyInvocation.Mycommand)" } #end <#PSScriptInfo .VERSION 1.0.0 .GUID d152442f-6e97-4b92-803b-dc91bf9f9b3a .AUTHOR Jeff Hicks .COMPANYNAME JDH Information Technology Solutions, Inc. .COPYRIGHT (c) 2017 JDH Information Technology Solutions, Inc. .TAGS SQL .LICENSEURI https://gist.github.com/jdhitsolutions/d97a81b86ca7a8f419f75acd33368ece .PROJECTURI https://gist.github.com/jdhitsolutions/4851ae5e3f85d80792f64809caf22fdc .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES This script is still being developed for cross-platform support in Linux. #> <# .DESCRIPTION This command uses the .NET SqlClient classes to connect to a SQL database and execute any type of query. The default behavior is to use integrated Windows authentication, but you can pass a credential object for a username and password. This works well when run on Linux. It does not require any of the SQL modules. #> |