Question:

How can I use Powershell to create an inventory of my databases?

Answer:

You can use SQL Server’s SMO structures to create an inventory of database objects. Once generated, you can output the result to the terminal in grid format, or others such as CSV or text files.

Specifically, using the name space: Microsoft.SqlServer.Management.Smo.  The Server class within this name space provides a wealth of information for the user to query.

I prefer building functions vs. simple scripts since you can import them into a module (function library) as you build your repertoire.   Start by declaring the function:

function Get-DBInventory
{
[CmdletBinding()]
[Parameter (Mandatory = $True,
ValueFromPipeline = $True )]
param ([string] $computerName,
[switch] $ToScreen)

I’ve defined our first parameter as a mandatory parameter as well as being able to pipe the value in from another function on the command line. The second parameter is a switch type, used as a Boolean.  The $ToScreen variable can be set to $true or $false depending on where the user prefers the output to be generated.  By choosing the argument -ToScreen at the command line, the variable will be set to $true.

Next, we need to import the SQLPS module:

BEGIN
{
Import-Module sqlps -DisableNameChecking | Out-Null
}

Next, we enter the Process block to do actual work. I’ll explain the snippets as we go.

PROCESS
{
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $computername -ErrorAction stop
$folder = “c:\temp”
$filename = “DB Inventory.csv”
$fullpath = Join-Path $folder $filename
$result = @()

In the example above, I’ve created a new object using the Smo.Server and assigned it to a variable $server. The –ArgumentList $computername tells PowerShell what server to use, and the –ErrorAction stop is what I want to happen if there is an error creating the object.  This is necessary when using a try/catch block.

I’ve also set up a hard-coded output file name and path (c:\temp) to place an output file should the user select an output file. $result is defined as an empty array to hold the output of our next command.

foreach ($db in $server.Databases)
{
Write-Verbose “Working DB: $db”
$item = $null
$hash = @{ “DBName”              =     $db.name
“CreateDate”      =   $db.CreateDate
“Owner”    =    $db.owner
“RecoveryModel”     =    $db.recoverymodel
“Size/MB”       =    $db.size
“DataSpaceUsage/KB” =    $db.dataspaceusage
“IndexSpaceUsage/KB” =    $db.indexspaceusage
“Collation”   =    $db.collation
“UserCount”   =    $db.users.count
“TableCount”       =    $db.tables.count
“SPCount”       =    $db.storedprocedures.count
“UDFCount”   =    $db.userdefinedfunctions.count
“ViewCount”           =    $db.Views.Count
“LastBUPDate”      =    $db.lastbackupdate
“LastDiffBUPDate”     =    $db.lastdifferentialbackupdate
“LastLogBUPDate”     =    $db.lastlogbackupdate
}
$item = New-Object PSObject -Property $hash
$result += $item
}

The for-each loop above goes through all the databases found in the $server object variable. I’ve defined a hash (key-value pair) to assign the output heading (key) and the information (value) found in the database variable – $db, using the properties found in the Database class.  *NOTE:  you can go to MSDN and look up these class[es] and specify your own properties to query.

An $item variable is created as a PSObject and assigned the values of the $hash. The $item variable is then appended to the array – $result.  The array will contain all the defined values for the specific database being queried.

Next is the output generation code.

if ($ToScreen)
{
$result |
Select DBName, createdate, owner,
recoverymodel, size/mb, dataspaceusage/kb,
indexspaceusage/kb, collation, usercount,
tablecount, spcount, udfcount, viewcount, lastbupdate,
lastdiffbupdate, lastlogbupdate |
Out-GridView -Title “SQL Server Inventory: $computername”
}
else
{
$result |
Select DBName, createdate, owner,
recoverymodel, size/mb, dataspaceusage/kb,
indexspaceusage/kb, collation, usercount,
tablecount, spcount, udfcount, viewcount, lastbupdate,
lastdiffbupdate, lastlogbupdate |
Export-Csv -Path $fullpath -NoTypeInformation
}

The Boolean $ToScreen is checked for $true or $false, if $true, then the output is sent to the screen. If $false, then the output is sent to a .CSV formatted file.  In any case, the array variable, $result, is piped to the Select-Object cmdlet (Select) where the output columns (the key) are selected.  These columns are then piped to the appropriate output cmdlet: Out-GridView or Export-CSV for viewing on the screen or saving to a .CSV file, respectively.

Finally, we close out our function definition:

} # End PROCESS block
END {}
} # End Function

Here is the grid output of the DB inventory on my laptop. *Note:  I’ve removed the owner and createdate columns.  You can use the Add Criteria to filter the returned data, or the filter text box at the very top left corner.  You can also select which columns to display by right-clicking any of the column headers.  (click on the image below to enlarge)

DBinventory