Listing Database Info with Object counts


Today, the same friend that asked me about the scripting (Quick Post–Scripting All Stored Procedures from All Databases with a command line) had a new problem. He needs to list in a Excel file preferably ,all the properties from all databases in all  SQL Server Instances and counting each database object. How many views, triggers …etc. We need power !!!

yoda

As he already set up his environment with SQLPSX, it was just to use it .

The first code was :

param([string[]]$ServerInstance)

 

foreach ($SQLInstance in $ServerInstance ) {

 

     $result =@()

    

     Get-SqlDatabase -sqlserver $SQLInstance | % {

    

          $Database = $_

          $Object  = New-Object psobject

 

          $Object |

          Add-Member -MemberType  NoteProperty -Name “DatabaseName” -Value $Database -PassThru | Out-Null

               

          $Database |

          Get-Member -MemberType “Property” |

          Where-Object {$_.definition -like  ‘*Microsoft.SqlServer.Management.Smo*’ -and $_.definition -like ‘*collection*’ } |

          foreach {

         

                $Value = Invoke-expression “(`$database.$($_.name) | where{( -not `$_.IsSystemObject)} | Measure-Object).count”

         

               $Object |

                Add-Member -MemberType NoteProperty -Name $_.name -Value $Value -PassThru -Force |Out-Null

          }

         

          $result += $Object

                    

     }

    

     $SQLInstance = $SQLInstance -replace “\\”,”_”

     $result |

     Export-Csv -Path “c:\temp\Databases\$($SQLInstance).csv” -NoTypeInformation -Force

 

     Convert-CSVToExcel -inputfile  “c:\temp\Databases\$($SQLInstance).csv”  -output “c:\temp\Databases\$($SQLInstance).xlsx” -verbose

 

}

   1: param([string[]]$ServerInstance)

   2:  

   3: foreach ($SQLInstance in $ServerInstance ) {

   4:  

   5:     $result =@()

   6:     

   7:     Get-SqlDatabase -sqlserver $SQLInstance | % {

   8:     

   9:         $Database = $_

  10:         $Object  = New-Object psobject 

  11:  

  12:         $Object | 

  13:         Add-Member -MemberType  NoteProperty -Name "DatabaseName" -Value $Database -PassThru | Out-Null 

  14:             

  15:         $Database | 

  16:         Get-Member -MemberType "Property" | 

  17:         Where-Object {$_.definition -like  '*Microsoft.SqlServer.Management.Smo*' -and $_.definition -like '*collection*' } | 

  18:         foreach {

  19:         

  20:             $Value = Invoke-expression "(`$database.$($_.name) | where{( -not `$_.IsSystemObject)} | Measure-Object).count"

  21:         

  22:             $Object | 

  23:             Add-Member -MemberType NoteProperty -Name $_.name -Value $Value -PassThru -Force |Out-Null

  24:         }

  25:         

  26:         $result += $Object

  27:                 

  28:     }

  29:     

  30:     $SQLInstance = $SQLInstance -replace "\\","_"

  31:     $result |

  32:     Export-Csv -Path "c:\temp\Databases\$($SQLInstance).csv" -NoTypeInformation -Force

  33:  

  34:     Convert-CSVToExcel -inputfile  "c:\temp\Databases\$($SQLInstance).csv"  -output "c:\temp\Databases\$($SQLInstance).xlsx" -verbose

  35:  

  36: }

Line 10  I am creating a new psobject and in the Line 12 I am adding the Database Name in this Object.

In the line 15 to 24 I am choosing only member type property and with “Microsoft.SqlServer.Management.Smo” and “Collection” in the definition. Why ?  I want to get the properties that are database objects in the SMO Database.I can be wrong, but what I identified is that database objects are SMO and has “Collection” in the type. The other properties are string, int..etc. and are not database objects but configurations, like collation.

The in the line 26 I am adding this object to an array to store all databases.

Then in the line 31 I am exporting to a CSV the result array with all databases.It creates one CSV for  each Server.

After that, in the line 34 I am using the Convert-CSVToExcel from the Jedi, Boe Prox to , of course, convert the CSV to Excel.

The output is, if you add the line $result in the line 29 is :

1

And the Excel file will looks like :

2

Ok. Then when he ran and liked the information, asked to me if we could add the other information from the databases (properties..collation..etc) . Yes..with a bit change in the code : Need to add before the filter to database objects :

$Database | select -ExpandProperty properties |

select name,value  | %{

     $Object |

     Add-Member -MemberType  NoteProperty -Name $_.name -Value $_.value -PassThru | Out-Null

}

 

The code is :

param([string[]]$ServerInstance)

 

foreach ($SQLInstance in $ServerInstance ) {

 

     $result =@()

    

     Get-SqlDatabase -sqlserver $SQLInstance | % {

          $Database = $_

         

          $Object  = New-Object psobject

 

          $Object |

          Add-Member -MemberType  NoteProperty -Name “DatabaseName” -Value $Database -PassThru | Out-Null

         

          $Database | select -ExpandProperty properties |

          select name,value  | %{

                $Object |

                Add-Member -MemberType  NoteProperty -Name $_.name -Value $_.value -PassThru | Out-Null

          }

 

                              

          $Database |

          Get-Member -MemberType “Property” |

          Where-Object {$_.definition -like  ‘*Microsoft.SqlServer.Management.Smo*’ -and $_.definition -like ‘*collection*’ } |

          foreach {

         

                $Value = Invoke-expression “(`$database.$($_.name) | where{( -not `$_.IsSystemObject)} | Measure-Object).count”

         

                $Object |

                Add-Member -MemberType NoteProperty -Name $_.name -Value $Value -PassThru -Force |Out-Null

          }

          $result += $Object

 

         

     }

    

     $SQLInstance = $SQLInstance -replace “\\”,”_”

     $result |

     Export-Csv -Path “c:\temp\Databases\$($SQLInstance).csv” -NoTypeInformation -Force

    

     Convert-CSVToExcel -inputfile  “c:\temp\Databases\$($SQLInstance).csv”  -output “c:\temp\Databases\$($SQLInstance).xlsx” -verbose

    

}

And now we have a Excel file with all Database information and the count of each database object.

Just save the code as .ps1 and call  or schedule in a SQL Agent Job :

SomeName.ps1 “ServerName”

or :

SomeName.ps1 “Server1”,”Server2\Inst1”

or to a SQL Server Instances in a txt file :

Somename.ps1 (get-content c:\servers.txt)

 

#PowerShellLifeSyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s