Get-MSSQLColumn.ps1
|
function Get-MSSQLColumn { <# .SYNOPSIS Return details on SQL columns for one or more tables .DESCRIPTION Return details on SQL columns for one or more tables This function depends on Invoke-SQLCMD2. Thanks to Chad Miller and all the contributors! Download this from http://poshcode.org/4137 and get it into your session before running this command. .FUNCTIONALITY SQL .PARAMETER table One or more tables to query .PARAMETER database SQL database to query. Directly mapped to Invoke-SQLCMD2. Get-Help Invoke-SQLCMD2 -Full for more info .PARAMETER allFields If specified, return all details from INFORMATION_SCHEMA.COLUMNS. If not specified, returns only column names for the specified table(s) .PARAMETER username username for query. Directly mapped to Invoke-SQLCMD2. Get-Help Invoke-SQLCMD2 -Full for more info .PARAMETER password password for query. Directly mapped to Invoke-SQLCMD2. Get-Help Invoke-SQLCMD2 -Full for more info .PARAMETER ServerInstance SQL Server Instance to query. Directly mapped to Invoke-SQLCMD2. Get-Help Invoke-SQLCMD2 -Full for more info .EXAMPLE #Display full details for each column in tables tblServerInfo and tblApplicationInfo, from server SomeServerInstance and database ServerDB Get-MSSQLColumn -table tblServerInfo, tblApplicationInfo -database ServerDB -ServerInstance SomeServerInstance -allFields | Out-GridView .EXAMPLE #Display all column names for tblServerInfo from server SomeServerInstance and database ServerDB Get-MSSQLColumn -table tblServerInfo -database ServerDB -ServerInstance SomeServerInstance #> [cmdletbinding()] param( [parameter( Mandatory=$true, Position=0, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true )] [string[]]$table, [parameter( Mandatory=$true )] [string]$database, [switch]$allFields, [string]$username = $null, [string]$password = $null, [parameter( Mandatory=$true )] [string]$ServerInstance ) Begin { #Including this here to avoid dependencies... function Invoke-Sqlcmd2 { <# .SYNOPSIS Runs a T-SQL script. .DESCRIPTION Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified. Paramaterized queries are supported. .INPUTS None You cannot pipe objects to Invoke-Sqlcmd2 .OUTPUTS System.Data.DataTable .EXAMPLE Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1" This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query. StartTime ----------- 2010-08-12 21:21:03.593 .EXAMPLE Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt" This example reads a file containing T-SQL statements, runs the file, and writes the output to another file. .EXAMPLE Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command. VERBOSE: hello world .NOTES Version History v1.0 - Chad Miller - Initial release v1.1 - Chad Miller - Fixed Issue with connection closing v1.2 - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation v1.3 - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type v1.4 - Justin Dearing <zippy1981 _at_ gmail.com> - Added the ability to pass parameters to the query. v1.4.1 - Paul Bryson <atamido _at_ gmail.com> - Added fix to check for null values in parameterized queries and replace with [DBNull] v1.5 - Joel Bennett - add SingleValue output option .FUNCTIONALITY PowerShell Language #> [CmdletBinding()] param( [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance, [Parameter(Position=1, Mandatory=$false)] [string]$Database, [Parameter(Position=2, Mandatory=$false)] [string]$Query, [Parameter(Position=3, Mandatory=$false)] [string]$Username, [Parameter(Position=4, Mandatory=$false)] [string]$Password, [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600, [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15, [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile, [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow","SingleValue")] [string]$As="DataRow", [Parameter(Position=9, Mandatory=$false)] [System.Collections.IDictionary]$SqlParameters ) if ($InputFile) { $filePath = $(resolve-path $InputFile).path $Query = [System.IO.File]::ReadAllText("$filePath") } $conn=new-object System.Data.SqlClient.SQLConnection if ($Username) { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout } else { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } $conn.ConnectionString=$ConnectionString #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller if ($PSBoundParameters.Verbose) { $conn.FireInfoMessageEventOnUserErrors=$true $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} $conn.add_InfoMessage($handler) } $conn.Open() $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) $cmd.CommandTimeout=$QueryTimeout if ($SqlParameters -ne $null) { $SqlParameters.GetEnumerator() | ForEach-Object { If ($_.Value -ne $null) { $cmd.Parameters.AddWithValue($_.Key, $_.Value) } Else { $cmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) } } > $null } $ds = New-Object system.Data.DataSet $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd) [void]$da.fill($ds) $conn.Close() switch ($As) { 'DataSet' { Write-Output ($ds) } 'DataTable' { Write-Output ($ds.Tables) } 'DataRow' { Write-Output ($ds.Tables[0]) } 'SingleValue' { Write-Output ($ds.Tables[0] | Select-Object -Expand $ds.Tables[0].Columns[0].ColumnName ) } } } #Invoke-SQLCMD2 #Make sure invoke-sqlcmd2 is available if(-not ( get-command invoke-sqlcmd2 -ErrorAction SilentlyContinue)) { Throw "This command relies on Invoke-SQLCMD2. Please obtain the latest version and dot source it prior to running this command.`nThis script was built using the code from here: http://poshcode.org/4137" } #Build up the parameters for the query #TODO: Add functionality to query for specific column names? $params = @{ Query = "SELECT $( if($allFields){"*"} else{"COLUMN_NAME"}) FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME = @table" ServerInstance = $ServerInstance ErrorAction = "Stop" Database = $database } if($username){ $params.add("username",$username) } if($password){ $params.add("password",$password) } } Process { foreach($sqlTable in $table) { #Build sql query parameters $sqlParams = @{ table = $sqlTable } #Run the query, continue with the next table if we fail try { write-verbose "Running Invoke-SQLCMD with parameters:`n$($params | out-string)`nSQL Parameters:`n$($sqlParams | out-string)" $results = Invoke-Sqlcmd2 @params -SqlParameters $sqlParams } catch { Write-Error "Error returning columns from table '$sqlTable' on instance '$ServerInstance': $_" continue } #display all fields or expand column name depending on params if($allFields) { $results } else { $results | select -ExpandProperty COLUMN_NAME } } } } |