inventory of sql server info

  • kevin.j.sexton

    SSC Veteran

    Points: 204

    I am trying to use this powershell script to pull SQL Server information and place it in a table. It works great if I pass the server names in a comma delimited list. But I need it to work by pulling the list from the CMS(Central Management Server). The output from that query is a list of servers (1 per line). It works for other scripts but not this one.

    Here is the whole script but it is the last few lines I am having a problem with.

    # Assume you have SQLServer PowerShell module installed
    # on the server where you execute this script

    Import-Module sqlserver -DisableNameChecking;

    function Get-SQLDBInventory
    Param( [Parameter(Mandatory=$false, ValueFromPipeline=$true)]
    [string[]]$ServerInstance = $env:computername

    [string]$qry = @"
    set nocount on;
    if object_id('tempdb..#t', 'U') is not null
    drop table #t;
    create table #t (
    ServerName varchar(128) default @@servername
    , DBName varchar(128) default db_name()
    , DBOwner varchar(128)
    , CreateDate datetime2
    , RecoveryModel varchar(12)
    , StateDesc varchar(60)
    , CompatibilityLevel int
    , DataFileSizeMB int
    , LogFileSizeMB int
    , DataUsageMB int
    , IndexUsageMB int
    , SizeMB decimal(17,2)
    , Collation varchar(60)
    , UserCount int
    , RoleCount int
    , TableCount int
    , SPCount int
    , UDFCount int
    , ViewCount int
    , DMLTriggerCount int
    , IsCaseSensitive bit
    , IsTrustWorthy bit
    , LastFullBackupDate datetime2
    , LastDiffBackupDate datetime2
    , LastLogBackupDate datetime2);

    insert into #t (DBName, DBOwner, CreateDate, RecoveryModel, StateDesc, CompatibilityLevel, IsCaseSensitive
    , IsTrustWorthy, Collation, LastFullBackupDate, LastDiffBackupDate, LastLogBackupDate)
    select name, suser_sname(owner_sid), create_date, recovery_model_desc, state_desc,compatibility_level
    , IsCaseSensitive=CAST(CHARINDEX(N'_CS_', collation_name) AS bit), is_trustworthy_on, Collation_Name
    , t.LastFullBackup, t.LastDiffBackup, t.LastLogBackup
    from master.sys.databases db
    outer apply ( SELECT
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
    FROM msdb.dbo.backupset b
    where b.database_name =
    ) t;

    EXEC master.dbo.sp_msforeachdb 'use [?]
    update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)
    , DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB
    , DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize
    , TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC
    , DMLTriggerCount=y.DC
    , UserCount = z.UC, RoleCount = z.RC
    from #t t
    outer apply (
    SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128
    , SUM(case when df.type in (1,3) then df.size else 0 end)/128
    FROM sys.database_files df
    ) u(DBSize, LogSize)
    outer apply(select DataUsageMB=sum(
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
    ) x
    outer apply
    ( select SC=Sum(case Type when ''P'' then 1 else 0 end)
    , DC=Sum(case Type when ''TR'' then 1 else 0 end)
    , TC=Sum(case Type when ''U'' then 1 end)
    , UC= sum(case when Type in (''TF'', ''IF'', ''FN'') then 1 else 0 end)
    , VC=Sum(case Type when ''V'' then 1 else 0 end)
    from sys.objects where object_id > 1024
    and type in (''U'',''P'',''TR'',''V'',''TF'',''IF'',''FN'')
    ) y
    outer apply
    ( select UC = sum(case when [Type] in (''G'',''S'',''U'') then 1 else 0 end)
    , RC = sum(case when Type = ''R'' then 1 else 0 end)
    from sys.database_principals
    where principal_id > 4
    ) z where t.DBName=db_name();
    $dt2 = new-object System.Data.DataTable;
    $dt2.columns.add((new-object System.Data.DataColumn('ServerName' , [System.String])));
    $dt2.columns.add((new-object System.Data.DataColumn('DBName' , [System.String])));
    $dt2.columns.add((new-object System.Data.DataColumn('DBOwner' , [System.String])));
    $dt2.columns.add((new-object System.Data.DataColumn('CreateDate' , [System.DateTime])));
    $dt2.columns.add((new-object System.Data.DataColumn('RecoveryModel' , [System.String])));
    $dt2.columns.add((new-object System.Data.DataColumn('StateDesc' , [System.String])));
    $dt2.columns.add((new-object System.Data.DataColumn('CompatibilityLevel' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('DataFileSizeMB' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('LogFileSizeMB' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('DataUsageMB' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('IndexUsageMB' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('SizeMB' , [System.Decimal])));
    $dt2.columns.add((new-object System.Data.DataColumn('Collation' , [System.String])));
    $dt2.columns.add((new-object System.Data.DataColumn('UserCount' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('RoleCount' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('TableCount' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('SPCount' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('UDFCount' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('ViewCount' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('DMLTriggerCount' , [System.Int32])));
    $dt2.columns.add((new-object System.Data.DataColumn('IsCaseSensitive' , [System.Boolean])));
    $dt2.columns.add((new-object System.Data.DataColumn('IsTrustWorthy' , [System.Boolean])));
    $dt2.columns.add((new-object System.Data.DataColumn('LastFullBackupDate', [System.DateTime])));
    $dt2.columns.add((new-object System.Data.DataColumn('LastDiffBackupDate', [System.DateTime])));
    $dt2.columns.add((new-object System.Data.DataColumn('LastLogBackupDate' , [System.DateTime])));

    foreach ($svr in $ServerInstance)
    { Write-verbose "processing:$svr"
    try {
    write-host "Processing $svr" -ForegroundColor Green
    $dt=invoke-sqlcmd -ServerInstance $svr -Database master -Query $qry `
    -QueryTimeout 120 -OutputAs DataTables;
    $dt2.merge($dt); #append result to $dt2
    $r = $dt2.NewRow()
    $r.ServerName = $svr;
    $r.DBName = 'Server-Unaccessible';
    write-Error "Error Processing$svr" ;
    }#foreach $svr
    Write-Output $dt2;

    #$dt2 = Get-SQLDBInventory -serverinstance 'server1','server2','server3'

    #it only pulls information for the 1st server and then exits.
    $allservers = Get-DbaCmsRegServer -SqlInstance localhost -Group CMS_SQLGROUP | Select-Object -Unique -ExpandProperty ServerName
    $dt2 = $allservers | get-sqldbinventory

    Write-SqlTableData -ServerInstance localhost -DatabaseName DBAdmin -SchemaName dbo -TableName DBInventory -InputData $dt2;
  • Site Owners

    SSC Guru

    Points: 80379

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Thom A

    SSC Guru

    Points: 98505

    Wouldn't you want to use a ForEach loop to process these? You're trying to pass the array to get-sqldbinventory but $ServerInstance is defined as a string, not an array.

    I don't have the modules installed, but I would expect something more like:

    foreach ($Server in $AllServers){
    Get-SqlDbInventory -ServerInstance $Server.Name #don't know what the actual name of the property is

    • This reply was modified 3 weeks, 4 days ago by  Thom A.


    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply