Apr 24

Read SQL tables and its content using PowerShell

When I first started with PowerShell, I mostly used to export my content to CSV files, and then work with these, but after a while, I figured it would be best to store the data in a SQL Database.

Using powershell to import the data to my tables is a lot better than having csv files all over the place 🙂

Importing the data is one thing, but using it is also very important, so I made two functions to read the tables I have in my database, and one to get me the  information in the table using dynamic parameter.
By using a dynamic parameter, I dont have to know the table name, as it selects it from the list of available tables in my database.

First you need to connect to your SQL server and database:
(Local SQL Express installation is what I am running)

$DBServer = "$env:UserDomain\SQLEXPRESS"
$DBName = "TmJr"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"

After connecting to my Database I can use the two functions created to get my information:

Function Get-Tables {
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "SELECT * FROM sys.Tables"
$tempSQL = $sqlCommand.ExecuteReader()
$Global:Table=New-Object System.Data.DataTable
$Global:Table | Select-Object Name, create_date,modify_Date,type_desc

This would give me a list looking like this:





And to work with the information within one of the tables, I would use my second function Get-TableData

Function Get-TableData {

## Create the Customer Collection (remmeber dynamic parameters needs to expand the property when creating the collection.)

DynamicParam {
# Set the dynamic parameters' name
$ParameterName = 'Table'

# Create the dictionary
$RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary

# Create the collection of attributes
$AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]

# Create and set the parameters' attributes
$ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
$ParameterAttribute.Mandatory = $true
$ParameterAttribute.Position = 1

# Add the attributes to the attributes collection

# Generate and set the ValidateSet
$arrSet = Get-Tables | Select-Object -expandproperty Name
$ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($arrSet)

# Add the ValidateSet to the attributes collection

# Create and return the dynamic parameter
$RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
$RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
return $RuntimeParameterDictionary

begin {
# Bind the parameter to a friendly variable
$Global:Table = $PsBoundParameters[$ParameterName]

process {
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection

$sqlCommand.CommandText = "SELECT * FROM $Global:Table"
$tempSQL = $sqlCommand.ExecuteReader()
$Global:TableData=New-Object System.Data.DataTable


If you would read more about Dynamic paramters in PowerShell, I recommend this blogpost:

Leave a Reply

Your email address will not be published.