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 !!!
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 :
And the Excel file will looks like :
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
