Powershell Script to Collect SQL Server Inventory

Problem

Imagine that you have been asked to provide a report of all SQL Server databases under your care. It might sound trivial, but it is something that most of us probably don’t have especially if you manage dozens or hundreds of SQL Server instances.

A quick answer might be to run a query against a set of registered servers you have already compiled, but what if you don’t even have that? Well, the PowerShell script I’m presenting within this tip aims to help you deal with this request.

Solution

Below is the complete script but to understand the code a bit better, I will be splitting the code into "sections" so that each can be explained in full detail. I’ll mark within the code where each section starts/ends.

#Section 1

The very first thing to address is the creation of the table that will contain the list of all databases for all SQL Server instances under your care. Ideally, this table is hosted in a central environment controlled by you.

The $dbListTableCreationQuery contains the query string to create the DBList table (you can name it whatever you want of course) if it doesn’t exist already.

The Invoke-Sqlcmd command is used to execute the table creation query in the SQL Server instance and database you specify.

If the table already exists, then it will be truncated so that this whole process stores only the very latest information.

You can alter the code a bit if you don’t want to erase the data and always keep the data for historic comparison purposes.

#Section 2

In this section we specify the list of SQL Server instances that PowerShell will traverse to fetch the list of databases contained in each instance.

The $instanceLookupQuery contains the query that returns the list of instances. For this part, it is necessary that you already populate a table with the list of instances under your care, and with the format outlined in the result set example I’m suggesting.

Once you have this information in place, the $instances variable will hold the result set of the SQL Server instances you have specified.

You might want to try first creating the "instances" table with 1 row and test the PowerShell script to make sure it works as advertised and then you can add more later.

#Section 3

This section contains the query that will obtain the list of user database for any given SQL Server instance. It will get the following information:

Instance name: Where the database is hosted
Database name: Current name of the database
Size: Size of the database (in megabytes) at the time of execution of the query
State: Current state of the database at the time of execution of the query (online, offline, standby, etc.)
Owner: Name of the user that currently owns the database
Timestamp: Current timestamp so you can know when the information was retrieved

Feel free to add or remove as much information as you want, just make sure that it perfectly aligns with the table design from #Section 1.

#Section 4

Based on the list of instances specified in #Section 2, we will now go and fetch the databases information per instance and then save that information in the central table from #Section 1.

The foreach ($instance in $instances){} block of code will help us with that because each iteration will be used to act against each individual instance from the list of instances.

The $results variable will contain the result of the query described in #Section 3, fetched for the instance being targeted.

After fetching the databases list from the SQL Server instance, a dynamic INSERT statement is built to be able to store the information in the table described in #Section 1. It is very basic, the complete result set of the list of databases obtained will be iterated one by one within the foreach ($result in $results){} block and basically from there is where the INSERT statement will come to life.

The INSERT is built in a way so that you can insert multiple rows with one INSERT statement. Otherwise the mechanics would have to be built around one INSERT statement per-row to be inserted.
After having the complete INSERT statement ready, a final trailing comma is stripped from the query string so that SQL Server doesn’t return an error when trying to execute the query. This is addressed in the $insert.Substring(0,$insert.LastIndexOf(',')) part of the final Invoke-Sqlcmd command.
For troubleshooting purposes, you can output the INSERT statement being generated by displaying the contents of the $insert variable.

PowerShell Script to Inventory SQL Server Instances

You might want to modify the Invoke-Sqlcmd command to include your own specific credentials to establish the proper connection.
Replace "xxxx" with the values that fit your use case.

$server = "xxxx"
$inventoryDB = "xxxx"
 
#Section 1 START
#Create the central table where you will store the information gathered from all the instances
$dbListTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'DBList' AND xtype = 'U')
CREATE TABLE [DBList](
   [instance] [varchar](255) NOT NULL,
   [database] [varchar](255) NOT NULL,
   [size] [int] NOT NULL,
   [state] [varchar](50) NOT NULL,
   [owner] [varchar](64) NOT NULL,
   [t.imestamp] [datetime] NULL
) ON [PRIMARY]
GO
"
Invoke-Sqlcmd -Query $dbListTableCreationQuery -Database $inventoryDB -ServerInstance $server
 
#Clean the DBList table
Invoke-Sqlcmd -Query "TRUNCATE TABLE DBList" -Database $inventoryDB -ServerInstance $server
#Section 1 END
 
#Section 2 START
#Fetch all the instances with the respective SQL Server Version
<#
   This is an example of the result set that your query must return
   ###################################################
   # name                     #  instance            #
   ###################################################
   # server1.domain.net,45000 #  server1             #
   # server1.domain.net,45001 #  server1\MSSQLSERVER1# 
   # server2.domain.net,45000 #  server2             #
   # server3.domain.net,45000 #  server3             #
   # server4.domain.net       #  server4\MSSQLSERVER2#
   ###################################################            
#>
 
#Put in your query that returns the list of instances as described in the example result set above
$instanceLookupQuery = "SELECT name, instance FROM instances" 
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
#Section 2 END
 
#Section 3 START
$dbListQuery = "
SELECT      SERVERPROPERTY('SERVERNAME') AS 'instance',
            sys.databases.name AS 'name',
            CONVERT(INT,SUM(size)*8.0/1024) AS 'size',
            sys.databases.state_desc AS 'state',
            suser_sname(sys.databases.owner_sid) AS 'owner',
            GETDATE() as 'timestamp'
FROM        sys.databases 
JOIN        sys.master_files
ON          sys.databases.database_id = sys.master_files.database_id
WHERE       sys.databases.name NOT IN('master','model','msdb','tempdb','distribution','SDBA')
GROUP BY    sys.databases.name, sys.databases.state_desc,sys.databases.owner_sid
ORDER BY    sys.databases.name
"
#Section 3 END
 
#Section 4 START
#For each instance, fetch the list of databases (along with a couple of useful pieces of information)
foreach ($instance in $instances){
   $results = Invoke-Sqlcmd -Query $dbListQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30
 
   #Perform the INSERT in the DBList table only if it returns information
    if($results.Length -ne 0){
 
      #Build the insert statement
      $insert = "INSERT INTO DBList VALUES"
      foreach($result in $results){        
         $insert += "
         (
         '"+$result['instance']+"',
         '"+$result['name']+"',
         "+$result['size']+",
         '"+$result['state']+"',
         '"+$result['owner']+"',
            '"+$result['timestamp']+"'
            ),
         "
       }
 
      #Store the results in the table created at the beginning of this script
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
   }
}
#Section 4 END
 
Write-Host "Done!"

After executing the script, the DBList table should contain values like this or whatever data your retrieved:

SQL Server database results from executing the PowerShell code

Column "size" is in MB.
This was tested against SQL Server instances with versions 2005, 2008, 2008 R2, 2012, 2014, 2016 and 2017.

Bonus – SQL Server Agent Job to Automate Data Collection

Here’s T-SQL code to create a job so that you can automate the collection of this information on a set basis, so that you can always have your inventory up-to-date.

Modify the @command=N'DBList.ps1' parameter to point to the exact path where you stored the PowerShell script.
You can change the frequency of the job to match your particular use case.

USE [msdb]
GO
 
/****** Object:  Job [Database List Inventory]    Script Date: 1/24/2019 10:53:19 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 1/24/2019 10:53:19 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Database List Inventory', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=0, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @description=N'Populates the table DBList which has the inventory of the databases', 
      @category_name=N'[Uncategorized (Local)]', 
      @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [run powershell]    Script Date: 1/24/2019 10:53:19 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run powershell', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=1, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'PowerShell', 
      @command=N'DBList.ps1', 
      @database_name=N'master', 
      @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DB List Inventory Schedule', 
      @enabled=1, 
      @freq_type=8, 
      @freq_interval=64, 
      @freq_subday_type=1, 
      @freq_subday_interval=12, 
      @freq_relative_interval=0, 
      @freq_recurrence_factor=1, 
      @active_start_date=20180729, 
      @active_end_date=99991231, 
      @active_start_time=50000, 
      @active_end_time=235959, 
      @schedule_uid=N'006d6b4f-fc4e-41f7-b65a-252fed82ccab'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Next Steps

The output of the PowerShell script has very basic information that I considered important first hand. However, you can add as much information you want to fit your particular use case.
At the end of the day, when management "urgently" asks for a databases inventory, you simply have to copy and paste the information returned from "SELECT * FROM DBList"… and voila!
There are a ton of other useful resources, posted by the members of the MSSQLTips community, that can be very helpful as well and might add additional tricks to what was presented in this tip.
I highly recommend this tip from Edwin Sarmiento, very good stuff in this tip.

Resources

Community

Subscribe