Don Hite at myITforum.com

PowerShell Mini SQL Query Analyzer

This PowerShell script will allow you to run or execute ad hock SQL queries against your SQL 2000 or SQL 2005 databases. The script will prompt you for a SQL server name, database or catalog name and finally will allow you to enter your ad hock SQL query and will in turn write the results to the PowerShell window.

I found that since I have several SQL databases to manage, such as SMS, MOM, MOSS and other in house databases I needed to run certain quick queries against the databases without the need to open the SQL 2000 Query Analyzer or the SQL 2005 Editor. By adding variables for the Server, Catalog and Query I made the code reusable so that I do not have to hard code all the various server and database names and try to maintain several PowerShell scripts that run or execute certain queries.

To test the script execute it and when prompted to do so enter your SQL server name and for the database name enter the Pubs database name and for the query enter the following:

Select Au_Fname, Au_Lname, Phone From Authors

PS1 Script:

$SqlServer = Read-Host "Enter SQL Server Name"

$SqlCatalog = Read-Host "Enter Database Name"

$SqlQuery = Read-Host "Enter SQL Query"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $SqlQuery

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

Clear

$DataSet.Tables[0]