T-SQL – Scripting Stored Procedure to Create backup copy on same database

This post is one of the request from OP.

Download SQL  Backup SP in the same database

The probelm statement 1:-

Create a backup copy of a group of stored procedures on the same database.  Using TSQL I need to find all stored procedures that meet a criteria (WHERE pr.name = ‘%_SomeSuffix’) and create that same procedure in the same database with a different name (‘%_SomeSuffix_BAK).

DECLARE @sql nvarchar(maxDECLARE @Name varchar(100)='AdventureWorks2008' 
        replace(mod.definition,pr.name,pr.name+'_BAK'FROM sys.procedures pr 
INNER JOIN sys.sql_modules mod ON pr.object_id = mod.object_id 
WHERE pr.Is_MS_Shipped = 0 AND pr.name LIKE '%PROC_RowCount' 
   SET @sql = REPLACE(@sql,'''','''''') 
   SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')' 

The probelm statement 2:-

Create a backup copy of a group of stored procedures on the same database.  Using TSQL I need to find all stored procedures that meet a criteria (WHERE definition = ‘%_SomeSuffix’) and create that same procedure in the same database with a different name (ProcName_BAK).

uspGetBillOfMaterials has the following search pattern in the definition


DECLARE @sql nvarchar(maxDECLARE @Name varchar(100)='AdventureWorks2008' 
        replace(mod.definition,pr.name,pr.name+'_BAK'FROM sys.procedures pr 
INNER JOIN sys.sql_modules mod ON pr.object_id = mod.object_id 
WHERE pr.Is_MS_Shipped = 0 AND mod.definition  LIKE '%WITH [BOM_cte]%' 
   SET @sql = REPLACE(@sql,'''','''''') 
   SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')' 
Posted in SQL, T-SQL | Tagged , , , | Leave a comment

SQL – List Primary key and Concatenate composite Keys into a single row separed by comma

The following query lists all the Primary keys of all the table. 

OP has requested to list all PK and composite PKs and concatenate all it’s composite PK column values into a single row separated by comma hence this post.

 In the above screenshot  the invoices and PK_TEST table has composite PK’s. Query to get the Composite PKs in a single row separated by comma
            SELECT COL.COLUMN_NAME +','  AS [text()] 
             FOR XML PATH ('') 
            ) COL 
 where T.COL is NOT NULL 



Posted in SQL | Tagged , , , | 3 Comments

USING SQL & POWERSHELL – Different Methods to List Databases WITH Recovery Models


One of op requested to list all the user defined databases where it has been configured with simple recovery model across ‘N’ servers.There are many ways to achieve the requirement. 

You can query sys.databases with sqlcmd utility to list all the details

select name,recovery_model_desc from sys.databases where database_id > 4 and recovery_model_desc='SIMPLE'
Master..xp_cmdshell 'for /f %j in (c :\servers.txt ) do sqlcmd -S %j  
-Q "select name,recovery_model_desc from sys.databases where database_id > 4 and recovery_model_desc=''SIMPLE''"  
 Download the code: -



Using Powershell

The function Get-RecoveryModel has three input parameters

  1. InputFile – List contains all the servers
  2. Recoverymodel – type of the recovery model that you want to do a search
  3. Database Flag – (TRUE OR FALSE – Include or exclude system databases )
PowerShell Script
    The function call requires three parameters Inputfile, RecoveryModel(Full,simple,bulklogged) and Database flag ($TRUE=System Database,$FALSE=User Database)  
    1.PS:\>Get-RecoveryModel -InputFile F:\PowerSQL\Server.txt -RecoveryModel "SIMPLE" -DatabaseFlag $FALSE 
    2.PS:\>Get-RecoveryModel -InputFile F:\PowerSQL\Server.txt -RecoveryModel "FULL" -DatabaseFlag $TRUE     
    Author     : Powershellsql@gmail.com 
Function Get-RecoveryModel 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO"| Out-Null 
 ForEach ($instance in Get-Content $InputFile) 
       $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server'$instance 
       $dbs=$s.Databases| where {$_.RecoveryModel -like "$RecoveryModel" -and  $_.IsSystemObject -eq $DatabaseFlag } 
       $dbs | select @{Name="ServerName"; Expression = {$Instance}}, Name, RecoveryModel 

Function call:-

PS:\>Get-RecoveryModel -InputFile F:\PowerSQL\Server.txt -RecoveryModel "SIMPLE" -DatabaseFlag $FALSE 
PS:\>Get-RecoveryModel -InputFile F:\PowerSQL\Server.txt -RecoveryModel "FULL" -DatabaseFlag $TRUE     
 Converting the output to HTML
$OutputFile = "F:\PowerSQL\RecoveryModel.htm" 
$ServerList = "F:\PowerSQL\Server.txt" 
$a = "" 
$a = $a + "BODY{background-color:peachpuff;}" 
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}" 
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}" 
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}" 
$a = $a + "" 
Function Get-RecoveryModel 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO"| Out-Null 
 ForEach ($instance in Get-Content $InputFile) 
       $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server'$instance 
       $dbs=$s.Databases| where {$_.RecoveryModel -like "$RecoveryModel" -and  $_.IsSystemObject -eq $DatabaseFlag } 
       $dbs | select @{Name="ServerName"; Expression = {$Instance}}, Name, RecoveryModel 
Get-RecoveryModel -InputFile $ServerList -RecoveryModel "SIMPLE" -DatabaseFlag $FALSE|ConvertTo-HTML -head $a -body "<H2>Database Recovery Model Information</H2>" | Out-File $OutputFile 

Posted in Uncategorized | Leave a comment

PowerShell- Monitoring Group Of Services on Group of Servers with Alternate Credential

This post explains how to monitor a multiple services on a group of servers with alternate credentials.

This script will allow you to read a list of servers from the input file and use credentials to connect remote/DMZ servers to pull service status information for a set of specific services and send services status information to all intended recipients

Get-Service cmdlet don’t accept credentials parameter hence I’ve used win32_service win32 class which accepts the credential parameter. You need to comment or uncomment the required portion of code.

Most of the time it’s not good practice to hard code the credentials. In that case you might need encrypt the password and use the secured code in the script. You need to run the below Powershell script to pull the encrypted password to c:\SecurePassword.txt and copy code in the script.

$password = read-host -prompt "Enter your Password"  
$secure = ConvertTo-SecureString $password -force -asPlainText  
ConvertFrom-SecureString $secure |Out-File c:\SecurePassword.txt
 In the below example, Test@2013##) password is encrypted and it’s content is shown below

 There are some instance where you are OK to store the password in the script itself. 

Refer the below screenshot and change the code according to your requirement


The Get-ServiceStatusReport

  1. Credential to connect to DMZ server
  2. HTML Ouptut
  3. Email Address validation

 The Function Get-ServiceStatusReport contains five parameters

  1. ComputerList – List of Servers
  2. ServiceName – Name of Services separated by comma
  3. SMTPMail – SMTP mail address
  4. FromID – Valid Email ID
  5. ToID – Valid Email ID

 Download the code 


Sample Call:-

Get-ServiceStatusReport -ComputerList C:\servers.txt -includeService  “Dfs”,”Dhcp” -To <pjayaram@app.com> -From <pjayaram@app.com> -SMTPMail <SMTPMail>



You can also refer the below link if you want to run the script with default account




The Function Get-ServiceStatusReport contains five parameters 
ComputerList – List of Servers 
ServiceName – Name of Services separated by comma 
SMTPMail – SMTP mail address 
FromID – Valid Email ID 
ToID – Valid Email ID 
Sample Call:- 
-ComputerList C:\servers.txt  
-includeService  "Dfs","Dhcp"  
-To <pjayaram@app.com> 
-From <pjayaram@app.com> 
-SMTPMail <SMTPMail> 
Function Get-ServiceStatusReport 
$script:list = $ComputerList 
New-Item -ItemType file $ServiceFilename -Force 
# Enter the Credentials details 
$password = read-host -prompt "Enter your Password"  
write-host "$password is password"  
$secure = ConvertTo-SecureString $password -force -asPlainText  
ConvertFrom-SecureString $secure |Out-File c:\SecurePassword.txt 
#Replace the $encrypted value from the contents of c:\SecurePassword.txt  
$encrypted = "01000000d08c9ddf0115d1118c7a00c04fc297eb01000000efa85d14f32d8648a2d335e29d3f57f6b"  
$user = "testwint"  
$password = ConvertTo-SecureString -string $encrypted  
$Credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$password 
<# Hard code the the Credentials details 
$User = “testcog” 
$Pass = ConvertTo-SecureString “testasd#%)” -AsPlainText -Force 
#contain the username and password in a variable 
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass 
# Function to write the HTML Header to the file 
Function writeHtmlHeader 
param($fileName$date = ( get-date ).ToString('yyyy/MM/dd'Add-Content $fileName "<html>" 
Add-Content $fileName "<head>" 
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>" 
Add-Content $fileName '<title>Service Status Report </title>' 
add-content $fileName '<STYLE TYPE="text/css">' 
add-content $fileName  "<!--" 
add-content $fileName  "td {" 
add-content $fileName  "font-family: Tahoma;" 
add-content $fileName  "font-size: 11px;" 
add-content $fileName  "border-top: 1px solid #999999;" 
add-content $fileName  "border-right: 1px solid #999999;" 
add-content $fileName  "border-bottom: 1px solid #999999;" 
add-content $fileName  "border-left: 1px solid #999999;" 
add-content $fileName  "padding-top: 0px;" 
add-content $fileName  "padding-right: 0px;" 
add-content $fileName  "padding-bottom: 0px;" 
add-content $fileName  "padding-left: 0px;" 
add-content $fileName  "}" 
add-content $fileName  "body {" 
add-content $fileName  "margin-left: 5px;" 
add-content $fileName  "margin-top: 5px;" 
add-content $fileName  "margin-right: 0px;" 
add-content $fileName  "margin-bottom: 10px;" 
add-content $fileName  "" 
add-content $fileName  "table {" 
add-content $fileName  "border: thin solid #000000;" 
add-content $fileName  "}" 
add-content $fileName  "-->" 
add-content $fileName  "</style>" 
Add-Content $fileName "</head>" 
Add-Content $fileName "<body>" 
add-content $fileName  "<table width='100%'>" 
add-content $fileName  "<tr bgcolor='#CCCCCC'>" 
add-content $fileName  "<td colspan='4' height='25' align='center'>" 
add-content $fileName  "<font face='tahoma' color='#003399' size='4'><strong>Service Stauts Report - $date</strong></font>" 
add-content $fileName  "</td>" 
add-content $fileName  "</tr>" 
add-content $fileName  "</table>" 
# Function to write the HTML Header to the file 
Function writeTableHeader 
Add-Content $fileName "<tr bgcolor=#CCCCCC>" 
Add-Content $fileName "<td width='10%' align='center'>ServerName</td>" 
Add-Content $fileName "<td width='50%' align='center'>Service Name</td>" 
Add-Content $fileName "<td width='10%' align='center'>status</td>" 
Add-Content $fileName "</tr>" 
Function writeHtmlFooter 
Add-Content $fileName "</body>" 
Add-Content $fileName "</html>" 
Function writeDiskInfo 
param($filename,$Servername,$name,$Statusif$status -eq "Stopped") 
 Add-Content $fileName "<tr>" 
 Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$servername</td>" 
 Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$name</td>" 
 Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$Status</td>" 
 Add-Content $fileName "</tr>" 
Add-Content $fileName "<tr>" 
 Add-Content $fileName "<td >$servername</td>" 
 Add-Content $fileName "<td >$name</td>" 
 Add-Content $fileName "<td >$Status</td>" 
Add-Content $fileName "</tr>" 
writeHtmlHeader $ServiceFileName 
 Add-Content $ServiceFileName "<table width='100%'><tbody>" 
 Add-Content $ServiceFileName "<tr bgcolor='#CCCCCC'>" 
 Add-Content $ServiceFileName "<td width='100%' align='center' colSpan=3><font face='tahoma' color='#003399' size='2'><strong> Service Details</strong></font></td>" 
 Add-Content $ServiceFileName "</tr>" 
 writeTableHeader $ServiceFileName 
#Change value of the following parameter as needed 
#List of programs to include 
#$InlcudeArray = $inlcudeService 
Foreach($ServerName in (Get-Content $script:list)) 
$service = Get-WMIObject Win32_Service -computer $ServerName -credential $Credentials 
if ($Service -ne $NULL) 
foreach ($item in $service) 
 Foreach($include in $includeService)  
 write-host $inlcude                                     
 if(($item.Name).Contains($include-eq $TRUE) 
    Write-Host  $item.MachineName $item.name $item.Status  
    writeDiskInfo $ServiceFileName $ServerName $item.name $item.Status  
Add-Content $ServiceFileName "</table>"  
writeHtmlFooter $ServiceFileName 
function Validate-IsEmail ([string]$Email) 
                return $Email -match "^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$" 
Function sendEmail   
$body = Get-Content $htmlFileName  
$body = New-Object System.Net.Mail.MailMessage $from$receipients$subject$body  
$body.isBodyhtml = $true 
$smtpServer = $MailServer 
$smtp = new-object Net.Mail.SmtpClient($smtphost$validfrom= Validate-IsEmail $from 
if($validfrom -eq $TRUE) 
$validTo= Validate-IsEmail $to 
if($validTo -eq $TRUE) 
write-output "Email Sent!!" 
write-output "Invalid entries, Try again!!" 
$date = ( get-date ).ToString('yyyy/MM/dd') 
sendEmail -from $From -to $to -subject "Service Status - $Date" -smtphost $SMTPMail -htmlfilename $ServiceFilename 
Posted in Uncategorized | 1 Comment

PowerShell – Perfmon Counters into CSV File – Multiple remote servers


This script can be used for exporting specified performance counter values from multiple remote target servers to CSV file. The challenge is to gather the specific counter information and append it to CSV file with Powershell2.0.

Performance counters

The script will pull the below mentioned counter values

  • Processor (_total)\% processor time
  • system\processor queue length
  • Memory\Available MBytes
  • Memory\% committed bytes in use
  • PhysicalDisk (*)\Current Disk Queue Length
  • PhysicalDisk (*)\Avg. Disk sec/Transfer
  • PhysicalDisk (*)\% Disk Time
  • PhysicalDisk (*)\Disk Read Bytes/sec
  • PhysicalDisk (*)\Disk Write Bytes/sec
  • Network Interface (*)\Bytes Total/sec


IT Administrators may want to analyze the performance the servers with the help of perfmon counters information’s.


  • The source machine should have PowerShell 2.0 installed

Download the code:- http://gallery.technet.microsoft.com/PowerShell-Perfmon-0f013da8


You can use this script in following ways:

1. Download the script.

2. Open the script file with Notepad or any other script editors (preferably Windows PowerShell ISE)

3.  Change the Input file path and Output file if required other than the default location as shown in below screenshot.

4. Counters can be selected based on your requirement by changing the below code

5. Save the file then run the script via PowerShell in “Run as administrator” mode.


 You can run this script manually or by scheduler task


How to create a scheduler task:

1. Open “Task Scheduler” (Goto START—RUN —Type Tasks and hit enter)

2. Click “Create task”

3. Pick a name, and choose “Run whether user is logged on or not”


Choose “Triggers” Tab, Click “New”

5. Specify option you like, and then click “OK” to create a trigger


Choose “Actions” tab, Click “New”

7. Copy following command to “Program/script” textbox, click C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

8Enter the path of the saved script file in “Add arguments (optionally)” textbox

As per the screenshot I saved the file under C:\Perform_script.ps1 hence I updated the add arguments text box as

C:\Perform_script.ps1; exit” 




# Script: Get-CounterStats 
# Author: Prashanth and Praveen 
# Comments: This script will collect the specific counters value from the multiple target machines/servers  
which will be used to analayze the performance of target servers. 
#Define Input and output filepath 
$servers=get-content "C:\servers.txt" 
#Actual script starts here  
function Global:Convert-HString {       
   )#End Param 
    Write-Verbose "Converting Here-String to Array" 
    $HString -split "`n" | ForEach-Object { 
        $ComputerName = $_.trim() 
        if ($ComputerName -notmatch "#") 
    # Nothing to do here. 
#Function to have the customized output in CSV format 
function Export-CsvFile { 
  SupportsShouldProcess=$true, ConfirmImpact='Medium')] 
[Parameter(Mandatory=$true, ValueFromPipeline=$true, 
[Parameter(Mandatory=$true, Position=0)] 
#region -Append  
[Parameter(ParameterSetName='Delimiter', Position=1)] 
# This variable will tell us whether we actually need to append 
# to existing file 
$AppendMode = $false 
 try { 
  $outBuffer = $null 
  if ($PSBoundParameters.TryGetValue('OutBuffer', [ref]$outBuffer)) 
      $PSBoundParameters['OutBuffer'] = 1 
  $wrappedCmd = $ExecutionContext.InvokeCommand.GetCommand('Export-Csv', 
                #String variable to become the target command line 
                $scriptCmdPipeline = '' 
                # Add new parameter handling 
                #region Dmitry: Process and remove the Append parameter if it is present 
                if ($Append) { 
                                $PSBoundParameters.Remove('Append'| Out-Null 
  if ($Path) { 
   if (Test-Path $Path) {         
    # Need to construct new command line 
    $AppendMode = $true 
    if ($Encoding.Length -eq 0) { 
     # ASCII is default encoding for Export-CSV 
     $Encoding = 'ASCII' 
    # For Append we use ConvertTo-CSV instead of Export 
    $scriptCmdPipeline +'ConvertTo-Csv -NoTypeInformation ' 
    # Inherit other CSV convertion parameters 
    if ( $UseCulture ) { 
     $scriptCmdPipeline +' -UseCulture ' 
    if ( $Delimiter ) { 
     $scriptCmdPipeline +" -Delimiter '$Delimiter' " 
    # Skip the first line (the one with the property names)  
    $scriptCmdPipeline +' | Foreach-Object {$start=$true}' 
    $scriptCmdPipeline +'{if ($start) {$start=$false} else {$_}} ' 
    # Add file output 
    $scriptCmdPipeline +" | Out-File -FilePath '$Path' -Encoding '$Encoding' -Append " 
    if ($Force) { 
     $scriptCmdPipeline +' -Force' 
    if ($NoClobber) { 
     $scriptCmdPipeline +' -NoClobber' 
 $scriptCmd = {& $wrappedCmd @PSBoundParameters } 
 if ( $AppendMode ) { 
  # redefine command line 
  $scriptCmd = $ExecutionContext.InvokeCommand.NewScriptBlock( 
} else { 
  # execute Export-CSV as we got it because 
  # either -Append is missing or file does not exist 
  $scriptCmd = $ExecutionContext.InvokeCommand.NewScriptBlock( 
# standard pipeline initialization 
$steppablePipeline = $scriptCmd.GetSteppablePipeline($myInvocation.CommandOrigin) 
 } catch { 
  try { 
  } catch { 
  try { 
  } catch { 
#Performance counters declaration 
function Get-CounterStats {  
    [String]$ComputerName = $ENV:ComputerName 
$Object =@() 
$Counter = @"  
Processor(_total)\% processor time  
system\processor queue length 
Memory\Available MBytes  
Memory\% committed bytes in use 
PhysicalDisk(*)\Current Disk Queue Length  
PhysicalDisk(*)\Avg. Disk sec/Transfer  
PhysicalDisk(*)\% Disk Time  
PhysicalDisk(*)\Disk Read Bytes/sec  
PhysicalDisk(*)\Disk Write Bytes/sec  
Network Interface(*)\Bytes Total/sec  
        (Get-Counter -ComputerName $ComputerName -Counter (Convert-HString -HString $Counter)).counterSamples |   
        ForEach-Object {  
        $path = $_.path  
        New-Object PSObject -Property @{ 
        Counter        = ($path  -split "\\")[-2,-1] -join "-" ; 
        Item        = $_.InstanceName ; 
        Value = [Math]::Round($_.CookedValue,2)  
        datetime=(Get-Date -format "yyyy-MM-d hh:mm:ss") 
#Collecting counter information for target servers 
foreach($server in $Servers) 
$d=Get-CounterStats -ComputerName $server |Select-Object computerName,Counter,Item,Value,datetime 
$d |Export-CsvFile $outfile  -Append -NoTypeInformation 
#End of Script






Posted in Uncategorized | 3 Comments

PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL

This script is used to discover all the server information and captures disk drive utilization details. The drive details are aggregated based on the status of free space. The aggregated data is pictorially represented using pie chart. The excel sheet is then send to intended users.

The script requires input file where all the servers are listed. You can also populate the server list by quering AD. The script will try to communicate all the servers which are listed in the input file to get the required information

Script Server Inventory on sheet1 and Disk Information on sheet2
Directory It checks for the directory C:\Scripts\ and create a one if it doesn’t exists.
Source File GetAllOsDrSrvInfo.PS1
Input File E:\Scripts\InputServer.txt
Output Excel sheet as an attachment
Email Notification Yes
Recipients More than one recipients can be separated by comma
SMTPMail SMTP address
Functionality Discovery all the server information and consolidate the data  in a excel sheet

Change the first few lines of colored code as per your requirement and change the alert threshold in the bottom of the code

#### Spreadsheet Location and email sending details

 $DirectoryToSaveTo = “c:\”

 $date=Get-Date -format “yyyy-MM-d”






 $Computers = Get-Content “c:\server.txt”


The code is tested on Powershell 2.0 onwards

Download the code : – OS and DiskInfo


#### Spreadsheet Location 
 $DirectoryToSaveTo = "c:\" 
 $date=Get-Date -format "yyyy-MM-d" 
 $Computers = Get-Content "c:\server.txt" 
# before we do anything else, are we likely to be able to save the file? 
# if the directory doesn't exist, then create it 
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing 
  New-Item "$DirectoryToSaveTo" -type directory | out-null 
#Create a new Excel object using COM  
$Excel = New-Object -ComObject Excel.Application 
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add() 
$Sheet = $Excel.Worksheets.Item(1) 
$sheet.Name = 'Server Inventory' 
#Create a Title for the first worksheet 
$row = 1 
$Column = 1 
$Sheet.Cells.Item($row,$column)= 'Server Inventory' 
$range = $Sheet.Range("a1","s2"$range.Merge() | Out-Null 
$range.VerticalAlignment = -4160 
#Give it a nice Style so it stands out 
$range.Style = 'Title' 
#Increment row for next set of data 
#Save the initial row so it can be used later to create a border 
#Counter variable for rows 
$intRow = $row 
#Read thru the contents of the SQL_Servers.txt file 
$Sheet.Cells.Item($intRow,1)  ="Name" 
$Sheet.Cells.Item($intRow,2)  ="status" 
$Sheet.Cells.Item($intRow,3)  ="OS" 
$Sheet.Cells.Item($intRow,4)  ="Domain Role" 
$Sheet.Cells.Item($intRow,5)  ="ProcessorName" 
$Sheet.Cells.Item($intRow,6)  ="Manufacturer" 
$Sheet.Cells.Item($intRow,7)  ="Model" 
$Sheet.Cells.Item($intRow,8)  ="SystemType" 
$Sheet.Cells.Item($intRow,9)  ="Last Boot Time" 
$Sheet.Cells.Item($intRow,10) ="Bios Version" 
$Sheet.Cells.Item($intRow,11) ="CPU Info" 
$Sheet.Cells.Item($intRow,12) ="NoOfProcessors" 
$Sheet.Cells.Item($intRow,13) ="Total Physical Memory" 
$Sheet.Cells.Item($intRow,14) ="Total Free Physical Memory" 
$Sheet.Cells.Item($intRow,15) ="Total Virtual Memory" 
$Sheet.Cells.Item($intRow,16) ="Total Free Virtual Memory" 
$Sheet.Cells.Item($intRow,17) ="Disk Info" 
$Sheet.Cells.Item($intRow,18) ="FQDN" 
$Sheet.Cells.Item($intRow,19) ="IPAddress" 
for ($col = 1; $col –le 19; $col++) 
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True 
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48 
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34 
Function GetStatusCode 
    Param([int] $StatusCode)   
        0         {"Success"} 
        11001   {"Buffer Too Small"} 
        11002   {"Destination Net Unreachable"} 
        11003   {"Destination Host Unreachable"} 
        11004   {"Destination Protocol Unreachable"} 
        11005   {"Destination Port Unreachable"} 
        11006   {"No Resources"} 
        11007   {"Bad Option"} 
        11008   {"Hardware Error"} 
        11009   {"Packet Too Big"} 
        11010   {"Request Timed Out"} 
        11011   {"Bad Request"} 
        11012   {"Bad Route"} 
        11013   {"TimeToLive Expired Transit"} 
        11014   {"TimeToLive Expired Reassembly"} 
        11015   {"Parameter Problem"} 
        11016   {"Source Quench"} 
        11017   {"Option Too Big"} 
        11018   {"Bad Destination"} 
        11032   {"Negotiating IPSEC"} 
        11050   {"General Failure"} 
        default {"Failed"} 
Function GetUpTime 
    param([string] $LastBootTime) 
    $Uptime = (Get-Date- [System.Management.ManagementDateTimeconverter]::ToDateTime($LastBootTime) 
    "Days: $($Uptime.Days); Hours: $($Uptime.Hours); Minutes: $($Uptime.Minutes); Seconds: $($Uptime.Seconds)"  
foreach ($Computer in $Computers) 
 TRY { 
 $OS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Computer 
 $Bios = Get-WmiObject -Class Win32_BIOS -ComputerName $Computer 
 $sheetS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Computer 
 $sheetPU = Get-WmiObject -Class Win32_Processor -ComputerName $Computer 
 $drives = Get-WmiObject -ComputerName $Computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} 
 $pingStatus = Get-WmiObject -Query "Select * from win32_PingStatus where Address='$Computer'" 
 $IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $Computer | ? {$_.IPEnabled}).ipaddress 
 $OSRunning = $OS.caption + " " + $OS.OSArchitecture + " SP " + $OS.ServicePackMajorVersion 
 $name=$SheetPU|select name -First 1 
 $ProcessorName=$SheetPU|select name -First 1 
 $DomainRole = $sheetS.DomainRole 
 $TotalAvailMemory = $OS.totalvisiblememorysize/1kb 
 $TotalVirtualMemory = $OS.totalvirtualmemorysize/1kb 
 $TotalFreeMemory = $OS.FreePhysicalMemory/1kb 
 $TotalFreeVirtualMemory = $OS.FreeVirtualMemory/1kb 
 $TotalMem = "{0:N2}" -$TotalAvailMemory 
 $TotalVirt = "{0:N2}" -$TotalVirtualMemory 
 $FreeMem = "{0:N2}" -$TotalFreeMemory 
 $FreeVirtMem = "{0:N2}" -$TotalFreeVirtualMemory 
 $date = Get-Date 
 $uptime = $OS.ConvertToDateTime($OS.lastbootuptime) 
 $BiosVersion = $Bios.Manufacturer + " " + $Bios.SMBIOSBIOSVERSION + " " + $Bios.ConvertToDateTime($Bios.Releasedate) 
 $sheetPUInfo = $name.Name + " & has " + $sheetPU.NumberOfCores + " Cores & the FSB is " + $sheetPU.ExtClock + " Mhz" 
 $sheetPULOAD = $sheetPU.LoadPercentage 
 if($pingStatus.StatusCode -eq 0) 
        $Status = GetStatusCode( $pingStatus.StatusCode ) 
    $Status = GetStatusCode( $pingStatus.StatusCode ) 
 if (($DomainRole -eq "0"-or ($DomainRole -eq "1")) 
 $Role = "Work Station" 
 elseif (($DomainRole -eq "2"-or ($DomainRole -eq "3")) 
 $Role = "Member Server" 
 elseif (($DomainRole -eq "4"-or ($DomainRole -eq "5")) 
 $Role = "Domain Controller" 
 $Role = "Unknown" 
 $pcnotfound = "true" 
 #### Pump Data to Excel 
 if ($pcnotfound -eq "true") 
 $sheet.Cells.Item($intRow, 1) = "$($computer) Not Found " 
 $sheet.Cells.Item($intRow, 1) = $computer 
 $sheet.Cells.Item($intRow, 2) = $status 
 $sheet.Cells.Item($intRow, 3) = $OSRunning 
 $sheet.Cells.Item($intRow, 4) = $Role 
 $sheet.Cells.Item($intRow, 5) = $name.name 
 $Sheet.Cells.Item($intRow, 6) = $Manufacturer 
 $Sheet.Cells.Item($intRow, 7) = $Model 
 $Sheet.Cells.Item($intRow, 8) = $SystemType 
 $sheet.Cells.Item($intRow, 9) = $uptime 
 $sheet.Cells.Item($intRow, 10)= $BiosVersion 
 $sheet.Cells.Item($intRow, 11)= $sheetPUInfo 
 $sheet.Cells.Item($intRow, 12)=$NoOfProcessors 
 $sheet.Cells.Item($intRow, 13)= "$TotalMem MB" 
 $sheet.Cells.Item($intRow, 14)= "$FreeMem MB" 
 $sheet.Cells.Item($intRow, 15)= "$TotalVirt MB" 
 $sheet.Cells.Item($intRow, 16)= "$FreeVirtMem MB" 
 $sheet.Cells.Item($intRow, 19)=$IPAddress 
 $sheet.Cells.Item($intRow, 18)=$FQDN 
$driveStr = "" 
 foreach($drive in $drives) 
 $size1 = $drive.size / 1GB 
 $size = "{0:N2}" -$size1 
 $free1 = $drive.freespace / 1GB 
 $free = "{0:N2}" -$free1 
 $freea = $free1 / $size1 * 100 
 $freeb = "{0:N2}" -$freea 
 $ID = $drive.DeviceID 
 $driveStr +"$ID = Total Space: $size GB / Free Space: $free GB / Free (Percent): $freeb % ` " 
 $sheet.Cells.Item($intRow, 17) = $driveStr 
$intRow = $intRow + 1 
 $pcnotfound = "false" 
$erroractionpreference = “SilentlyContinue”  
$Sheet = $Excel.Worksheets.Item(2) 
$sheet.Name = 'DiskSpace' 
$Sheet.Activate() | Out-Null 
#Create a Title for the first worksheet 
$row = 1 
$Column = 1 
$Sheet.Cells.Item($row,$column)= 'Disk Space Information' 
$range = $Sheet.Range("a1","h2"$range.Merge() | Out-Null 
$range.VerticalAlignment = -4160 
#Give it a nice Style so it stands out 
$range.Style = 'Title' 
#Increment row for next set of data 
#Save the initial row so it can be used later to create a border 
$initalRow = $row 
#Create a header for Disk Space Report; set each cell to Bold and add a background color 
$Sheet.Cells.Item($row,$column)= 'Computername' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column)= 'DeviceID' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column)= 'VolumeName' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column)= 'TotalSizeGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column)= 'UsedSpaceGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column)= 'FreeSpaceGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column)= '%Free' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column)= 'State' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
#Set up a header filter 
$headerRange = $Sheet.Range("a3","h3"$headerRange.AutoFilter() | Out-Null 
#Increment Row and reset Column back to first column 
$Column = 1 
#Get the drives and filter out CD/DVD drives 
foreach ($computer in $Computers) 
$diskDrives = Get-WmiObject win32_LogicalDisk -Filter "DriveType='3'" -ComputerName $computer 
#Process each disk in the collection and write to spreadsheet 
ForEach ($disk in $diskDrives) { 
    $Sheet.Cells.Item($row,1)= $disk.__Server 
    $Sheet.Cells.Item($row,2)= $disk.DeviceID 
    $Sheet.Cells.Item($row,3)= $disk.VolumeName 
    $Sheet.Cells.Item($row,4)= [math]::Round(($disk.Size /1GB),2) 
    $Sheet.Cells.Item($row,5)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2) 
    $Sheet.Cells.Item($row,6)= [math]::Round(($disk.FreeSpace / 1GB),2) 
    $Sheet.Cells.Item($row,7)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))   
    #Determine if disk needs to be flagged for warning or critical alert 
    If ($disk.FreeSpace -lt 5GB -AND ("{0:P}" -f ($disk.FreeSpace / $disk.Size))  -lt 40) { 
        $Sheet.Cells.Item($row,8) = "Critical" 
        #Check to see if space is near empty and use appropriate background colors 
        $range = $Sheet.Range(("A{0}"  -$row),("H{0}"  -$row)) 
        $range.Select() | Out-Null     
        #Critical threshold          
        $range.Interior.ColorIndex = 3 
    } ElseIf ($disk.FreeSpace -lt 10GB -AND ("{0:P}" -f ($disk.FreeSpace / $disk.Size)) -lt 60) { 
        $Sheet.Cells.Item($row,8) = "Warning" 
        $range = $Sheet.Range(("A{0}"  -$row),("H{0}"  -$row)) 
        $range.Select() | Out-Null         
        $range.Interior.ColorIndex = 6 
    } Else { 
        $Sheet.Cells.Item($row,8) = "Good" 
#Add a border for data cells 
$dataRange = $Sheet.Range(("A{0}"  -$initalRow),("H{0}"  -$row)) 
7..12 | ForEach { 
    $dataRange.Borders.Item($_).LineStyle = 1 
    $dataRange.Borders.Item($_).Weight = 2 
#Auto fit everything so it looks better 
$usedRange = $Sheet.UsedRange                                                             
$usedRange.EntireColumn.AutoFit() | Out-Null 
$sheet = $excel.Worksheets.Item(2)  
$beginChartRow = $Row 
$Sheet.Cells.Item($row,$Column) = 'Critical' 
$Sheet.Cells.Item($row,$Column) = 'Warning' 
$Sheet.Cells.Item($row,$Column) = 'Good' 
$Column = 1 
#Critical formula 
#Warning formula 
#Good formula 
$Sheet.Cells.Item($row,$Column)= $good 
$endChartRow = $row 
$chartRange = $Sheet.Range(("A{0}" -$beginChartRow),("C{0}" -$endChartRow)) 
##Add a chart to the workbook 
#Open a sheet for charts 
$temp = $sheet.Charts.Add() 
$chart = $sheet.Shapes.AddChart().Chart 
#Configure the chart 
##Use a 3D Pie Chart 
$chart.ChartType = 70 
$chart.Elevation = 40 
#Give it some color 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.TintAndShade = .34 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5 
$sheet.Shapes.Item("Chart 1").Fill.BackColor.TintAndShade = .765 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5 
$sheet.Shapes.Item("Chart 1").Fill.TwoColorGradient(1,1) 
#Set the location of the chart 
$sheet.Shapes.Item("Chart 1").Placement = 3 
$sheet.Shapes.Item("Chart 1").Top = 30 
$sheet.Shapes.Item("Chart 1").Left = 600 
$chart.SetSourceData($chartRange$chart.HasTitle = $True 
$chart.ChartTitle.Text = "Disk Space Report" 
$chart.ChartStyle = 26 
$chart.PlotVisibleOnly = $False 
$chart.SeriesCollection(1).DataLabels().ShowValue = $True 
$chart.SeriesCollection(1).DataLabels().Separator = ("{0}" -f [char]10) 
$chart.SeriesCollection(1).DataLabels().Position = 2 
$chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = 255 
$chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = 65535 
$chart.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = 5287936 
#Hide the data 
$chartRange.EntireRow.Hidden = $True 
$sheet.Name = 'DiskInformation' 
$filename = "$DirectoryToSaveTo$filename.xlsx" 
if (test-path $filename ) { rm $filename } #delete the file if it already exists 
$Excel.SaveAs($filename$xlOpenXMLWorkbook#save as an XML Workbook (xslx) 
$Excel.Saved = $True 
$Excel.DisplayAlerts = $False 
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath) 
#initate message 
$email = New-Object System.Net.Mail.MailMessage  
$email.From = $emailFrom 
$email.To.Add($emailTo$email.Subject = $subject 
$email.Body = $body 
# initiate email attachment  
$emailAttach = New-Object System.Net.Mail.Attachment $filePath 
#initiate sending email  
$smtp = new-object Net.Mail.SmtpClient($smtpServer$smtp.Send($email) 
#Call Function  
$message = @"  
Hi Team, 
The Discovery of Windows Server and Disk Space information for all the listed instances. 
Autogenerated Email!!! Please do not reply. 
Thank you,  
sendEmail -emailFrom $fromEmail -emailTo $ToEmail -subject "Windows Server Inventory & Disk Details -$($date)" -body $message -smtpServer $SMTPMail -filePath $filename 
 Sheet1 – Server Information
Sheet 2 – Disk Information – No of critical, warning and good state representation of drives




Posted in Uncategorized | Tagged , , , , , | Leave a comment

SQL – List All Tables, Space Usage Info & Other Details

One of the OP requested in the forum to find all table details of any database. The details which includes Number of Rows,table size, index size etc:-. Even I frequently writing queries to list all such details.

I’ve used SQLCMD mode to connect to the remote server and database to fetch all the required details.

SQLCMD can be enabled in Query Editor.

Go to SSMS Menu -> Query ->Select SQLCMD.

 Download the script :- SQL-List-All-Tables-Information

Chane the Servername and Databasename in the below script

USE $(DB) 
create table #TableSize ( 
    Name varchar(255), 
    reserved varchar(255), 
    data varchar(255), 
    index_size varchar(255), 
    unused varchar(255)) 
create table #ConvertedSizes ( 
    Name varchar(255), 
    reservedKb int, 
    dataKb int, 
    reservedIndexSize int, 
    reservedUnused int) 
EXEC sp_MSforeachtable @command1="insert into #TableSize 
EXEC sp_spaceused '?'insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused) 
select name, [rows],  
SUBSTRING(unused0LEN(unused)-2from #TableSize 
select * from #ConvertedSizes 
order by reservedKb desc 
drop table #TableSize 
drop table #ConvertedSizes

Posted in Uncategorized | Tagged , , , , , | Leave a comment

PowerShell-Input-Validation Scripts

Input values are validated using various mechanisms.  let’s start by examining how you would validate input parameters in Windows PowerShell 2.0

The Inputs are validated for the below mentioned functionality

  1. PostalCode
  2. PhoneNumber
  3. EmailID
  4. IP 
  5. FileName

Download the scripts from the below link


To validate PostalCode – 99999

Function Validate-PostalCode 
 Param([Parameter(Mandatory=$true,HelpMessage="Enter a valid Postal Code xxx-xxx-xxxx")][ValidatePattern("[0-9][0-9][0-9][0-9]")]$PostalCode) 
 Write-host "The Pin Code $PostalCode is valid" 

To Validate phone number –  999-999-9999

Function validate-PhoneNumber 
  Write-host "The phone number $phoneNumber is valid" 
validate-PhoneNumber -Phonenumber 999-999-9999
 To Validate email address
function Validate-Email ([string]$Email) 
  return $Email -match "^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$" 
 To Validate IP Range – 
function Validate-IPAddress ([string]$IP) 
 if($IP -match "(\d{1,3}).(\d{1,3}).(\d{1,3}).(\d{1,3})" -and -not ([int[]]$matches[1..4] -gt 255)) 
   Write-host "The $IP IP is valid" 
 To Validate the filename for ########_???_?.jpg 
function Validate-Filename 
Write-host "The filename $filename is valid" 
You can also refer the below link for more information
Posted in Uncategorized | Tagged , , , , , | Leave a comment

SQL – Find encrypted objects using sql_modules or Powershell

sys.sql_modules catalog view which got introduced from SQL 2005 onwards. The sql_modules which includes the objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module.


OBJECT_NAME(sm.object_id) AS object_name, 
o.type, o.type_desc, 
CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] 
FROM sys.sql_modules AS sm 
JOIN sys.objects AS o ON sm.object_id = o.object_id 
ORDER BY o.type;
sql_modules to list only stored procedures then you can join with sys.procedures
WHEN SM.definition IS NULL THEN 'Encyrpted' END AS IsEncrypted 
FROM sys.procedures AS SP     
LEFT JOIN sys.sql_modules AS SM    ON SP.object_id = SM.object_id 
 PowerShell :-

Change the Servername and DBName in the below code. Execute the below code from PowerShell-ISE

$server = "<ServerName>" 
$database = "<DBName>" 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null 
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server 
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database") 
# Get the database and Its objects 
$db = $srv.Databases[$database] 
$storedProcs = $db.StoredProcedures | Where-object {$_.IsEncrypted} 
$storedProcs|select name
 Download the code: -

Posted in Uncategorized | Tagged , | Leave a comment

First Birthday to My Blog

Today is the First year anniversary of my blog. After thinking a lot and going through many un-imaginable resource available over the internet and reading blogs of many wonderful brains made me think about creating a blog.

When I wrote my first post a year ago I wondered and asked myself many times what I’m going to tell you all.  I refused myself many times. At lost I made it and completed first year. Later I started enjoying in what I’m doing. It’s great to share what I do, but mostly I enjoy the feedback I get from you all.  This is really invaluable.  It tells me what works and what doesn’t and many times inputs and advice’s is truly priceless.

 Thanks everyone for contributing to the world of knowledge. I truly admire everyone.

 Happy Learning!!!!!!!!!!!


Posted in Uncategorized | Tagged | Leave a comment