Powershell Script to Collect SQL Server Inventory

$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!"			

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