Find Active Connections in SQL Server Using PowerShell

Today i would like to show you simple code to find the active connections in SQL Server using PowerShell. PowerShell code is very easy to write. All you need is the Class names to connect to the Database to do the required job.

In Simple Steps:
1) Load the SMO object into the memory.
2) Create a new instance of SqlConnection Object
3) Assign the connection string.
4) Open the connection.
5) Create a new instance of SqlCommand Object.
6) Build the query
7) Set the Connection to Command Object
8) Execute the Command and store the results in DataReader.
9) Use While Loop to display the information.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$oConn = New-Object -TypeName System.Data.SqlClient.SqlConnection
$oConn.ConnectionString = "SERVER=WNYCDEF3Q9W721\VJ_LCL;Integrated Security = True"
$oConn.Open()

$Cmd = New-Object -TypeName system.data.sqlclient.sqlcommand
$sSQL = "SELECT db_name(dbid) as Database_Name, "
$sSQL = $sSQL + " count(dbid) as No_Of_Connections "
$sSQL = $sSQL + " FROM sys.sysprocesses "
$sSQL = $sSQL + " WHERE dbid > 0 "
$sSQL = $sSQL + " GROUP BY dbid "
$cmd.Connection = $oConn
$Cmd.CommandText = $sSQL
$data_reader = $Cmd.ExecuteReader()
$DB_Name
$No_Of_Conn

while ($data_reader.Read())
{

$DB_Name = $data_reader.GetString(0)
$No_Of_Conn = $data_reader.GetInt32(1)
Write-host "Database Name:" $DB_Name " " "No_Of_Connections:" $No_Of_Conn " "
}
$oConn.Close()