Is PowerShell installed on SQL Server?

Many database related tasks can be automated using PowerShell. This T-SQL script can be used to check the following:

1. Is PowerShell installed on your local SQL Server?
2. Is it enabled for script execution?
3. What is the version of PowerShell installed on your machine?

You can obtain all the above information using the below T-SQL script. Since we have to obtain information from the server registry and from the output of PowerShell.exe file, we will be using both xp_regread and xp_cmdshell extended stored procedures in this example:

--Check if PowerShell is installed and if yes, what version and if if it's enabled for execution
--Author: Saleem Hakani
SET NOCOUNT ON
Declare @PowerShell_Check Table 
(
    IsPowershellInstalled int null,
    IsExecutionPolicyEnabled Varchar(20) null,
    CheckPowerShellVersion Varchar(20) null
)
INSERT INTO @PowerShell_Check (IsPowershellInstalled) Exec master.sys.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\PowerShell\1'
INSERT INTO @PowerShell_Check (IsExecutionPolicyEnabled) EXEC master.dbo.xp_cmdshell N'%WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy"'
INSERT INTO @PowerShell_Check (CheckPowerShellVersion) EXEC master.dbo.xp_cmdshell N'%WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-Host | Format-Table -Property Version"'
If Exists (Select * from @PowerShell_Check where IsPowershellInstalled=1)
Begin
    INSERT INTO @PowerShell_Check (IsExecutionPolicyEnabled) EXEC master.dbo.xp_cmdshell N'%WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy"'
    If Not Exists (Select IsExecutionPolicyEnabled from @PowerShell_Check where IsExecutionPolicyEnabled in ('RemoteSigned','Unrestricted'))
    Begin
        Print '[WARNING: Execution of Powershell scripts is disabled on this system]'
        Print 'To change the execution policy, type the following command in Powershell console: Set-ExecutionPolicy RemoteSigned'
    End
    Else 
    Begin
        Select 'PowerShell version '+(Select Left(CheckPowerShellVersion,3) from @PowerShell_Check where CheckPowerShellVersion is not null and IsNumeric(CheckPowerShellVersion)=1)+' is installed and is enabled on this machine '+'('+@@ServerName+')'
    End
End

Note: That the user running the above script must have sysadmin permissions to use both sys,xp_regread and xp_cmdshell.