SQL Server Metadata using Powershell

If you are new at your job or a new client you would like to gather and review the sql server meta data to understand the environment and get up to speed as quickly as possible.

Most of us are already familiar with the DMVs, system functions, procedures etc. to gather the SQL server metadata.

And if you want to gather the information for all your SQL servers, you could run a multi-server query against all through the Central Management Server. In fact, in newer versions of SSMS you don't even need the CMS, you just register all your sql instances in the Local Server Groups.

So from that perspective this post is not adding much values except maybe that it is another alternative to SSIS or other ETL tools to capture the meta data on regular basis.

If nothing else I hope you find this educational regarding how to use powershell to interact with sql servers.

<#
Export SQL Server properties, settings and configuration values to CSV files
#>

# name of the sql server instance you would like to gather information about
$instanceName = "SQLServer1\SQL2016AG01"

# the folder where the csv files will be stored, by default it will be the current folder
$folder_path = ""

if ($folder_path -eq "" -or $folder_path -eq $null)
{
        $folder_path = (Get-Item -Path ".\").FullName
}

"Folder Path: " + $folder_path

# variables to hold the names of the csv files where information will be stored

$export_file_name_info     = $folder_path + "\" + ($instanceName -replace '\\', '_') + '_info.csv'
$export_file_name_settings = $folder_path + "\" + ($instanceName -replace '\\', '_') + '_settings.csv'
$export_file_name_config   = $folder_path + "\" +  ($instanceName -replace '\\', '_') + '_config.csv'


# Load the sql server assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# connect to the sql instance and gather some basic info
# Notice that I have not specified SQL credentials as I would like to use the current user's windows credentials
$instance_object = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$host_name       = $instance_object.NetName
$host_ip         = ([System.Net.Dns]::GetHostAddresses("$host_name") | ? {$_}).IPAddressToString
$db_count=$instance_object.Databases.count

"Host Name: "  + $host_name
"IP Address: " + $host_ip
"Databases: "  + $db_count

# Load up all the information into arrays
$sql_instance_info     = $instance_object.Information.Properties |Select Name, Value 
$sql_instance_settings = $instance_object.Settings.Properties |Select Name, Value 
$sql_instance_config   = $instance_object.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue

$sql_instance_info
$sql_instance_settings
$sql_instance_config

<#
Here I can simply export these arrays to respective CSV files but then it will be in the tabular format.
But I want the information to be displayed in wide (transposed) format.
That way if I want to gather information on multiple servers and stored in the same files, I can do that.

It takes little bit of additional coding but I think the results are worth it.
#>



# SQL Server properties

$info = New-Object -TypeName PSObject

$info | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$info | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$info | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count

foreach ($info_value in $sql_instance_info) 
{


    $Name  = $info_value.Name
    $value = $info_value.Value
    $info  | Add-Member -MemberType NoteProperty -Name $Name -Value $value
}


# SQL Server settings

$settings = New-Object -TypeName PSObject

$settings | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$settings | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$settings | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count


foreach ($settings_value in $sql_instance_settings) 
{
   $Name     = $settings_value.Name
   $value    = $settings_value.Value
   $settings | Add-Member -MemberType NoteProperty -Name $Name -Value $value
}


# SQL Server configuration (sp_configure)
# we need two custom objects here to store the configured as well as run values from sp_configure
$config_value   = New-Object -TypeName PSObject
$run_value      = New-Object -TypeName PSObject


$config_value | Add-Member -MemberType NoteProperty -Name ValueType -Value "Config Value"
$run_value    | Add-Member -MemberType NoteProperty -Name ValueType -Value "Run value"

$config_value | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$config_value | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$config_value | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count

$run_value    | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$run_value    | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$run_value    | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count


foreach ($c in $sql_instance_config) 
{
   $DisplayName  = $c.DisplayName
   $Description  = $c.Description
   $RunValue     = $c.RunValue
   $ConfigValue  = $c.ConfigValue
   $config_value | Add-Member -MemberType NoteProperty -Name $DisplayName -Value $ConfigValue
   $run_value    | Add-Member -MemberType NoteProperty -Name $DisplayName -Value $RunValue
   
   
   
}

# combine the config and run values in a single array
$config = @($config_value,$run_value)


# export the arrays into respective CSV files

$info     | Export-Csv -Path $export_file_name_info -NoTypeInformation
$settings | Export-Csv -Path $export_file_name_settings -NoTypeInformation
$config   | Export-Csv -Path $export_file_name_config -NoTypeInformation