I have probably been looking at this for too long to figure this out…
- What is the easiest way I could get this exported to a CSV format?
- Would it be better to dump this to a table in a database more
easily? I will be running this from a central server that I do have
an instance running.
The function below is for the purpose of pulling information about one or multiple databases of a SQL Server instance. So far I know this works on SQL 2005 and up.
I have used Format-Table
but that ends up cutting off columns. I have used Output-File
which can work but defaults to a list view which could be a pain to get into Excel. It also cuts off column data that exceeds a certain length.
EDIT: Purpose of this function is to run it against 100+ servers for gathering inventory of each instance.
PowerShell Function:
# Load SMO
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
function Get-DatabaseInfo ($server,$dbname)
{
$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
$db = $srv.Databases.Item($dbname)
$DataFile = $db | Select -ExpandProperty FileGroups | Select -ExpandProperty Files
$LogFile = $db | Select -ExpandProperty LogFiles
$tables = $db | Select -ExpandProperty tables | ? {$_.IsSystemObject -eq $false}
$indexes = $tables | Select -ExpandProperty Indexes | ? {$_.IsSystemObject -eq $false}
$srv.Databases.Item($dbname) | Select @{Label="*****************Database Name*****************";Expression={$_.Name}}
Write-Host "Database information for $dbname" -ForegroundColor red
$db |
Select @{Label="DateCaptured";Expression={Get-Date -Format yyyyMMdd-HHmm}},
ID, Name, Owner, CreateDate,
CompatibilityLevel, RecoveryModel,
LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate, LogReuseWaitStatus,
ActiveConnections,
AutoClose, AutoShrink,
AutoCreateStatisticsEnabled, AutoUpdateStatisticsEnabled,
Collation,
@{Label="DataSpaceUsage (KB)";Expression={$_.DataSpaceUsage}},
@{Label="IndexSpaceUsage (KB)";Expression={$_.IndexSpaceUsage}},
@{Label="SpaceAvailable (KB)";Expression={$_.SpaceAvailable}},
@{Label="Size (MB)";Expression={$_.Size}},
IsSQLCLREnabled,
IsMirroringEnabled, PageVerify, ReplicationOptions
Write-Host "Database File Level information for $dbname" -Foreground red
$DataFile | Select Name, Filename, Growth, GrowthType,
@{Label="MaxSize (MB)";Expression={$value = $_.MaxSize; switch($value){-1 {"Unlimited"} default {"{0:N2}" -f($value/1024)}}}},
@{Label="SizeAllocated (MB)";Expression={"{0:N2}" -f($_.Size/1024)}},
@{Label="UsedSpace (MB)";Expression={"{0:N2}" -f($_.UsedSpace/1024)}},
@{Label="Data % Full";Expression={"{0:N2}" -f(($_.UsedSpace/$_.Size) * 100)}},
@{Label="Data Space Left (MB)";Expression={"{0:N2}" -f(($_.Size/1024)-($_.UsedSpace/1024))}},
@{Label="Data % Available";Expression={"{0:N2}" -f((($_.Size/1024)-($_.UsedSpace/1024)) / ($_.Size/1024))}}
$logfile | Select Name, Filename, Growth, GrowthType,
@{Label="MaxSize (MB)";Expression={$value = $_.MaxSize; switch($value){-1 {"Unlimited"} default {"{0:N2}" -f($value)}}}},
@{Label="SizeAllocated (MB)";Expression={"{0:N2}" -f($_.Size/1024)}},
@{Label="UsedSpace (MB)";Expression={"{0:N2}" -f($_.UsedSpace/1024)}},
@{Label="Log % Full";Expression={"{0:N2}" -f(($_.UsedSpace/$_.Size) * 100)}},
@{Label="Log Space Left (MB)";Expression={"{0:N2}" -f(($_.Size/1024)-($_.UsedSpace/1024))}},
@{Label="Log % Available";Expression={"{0:N2}" -f((($_.Size/1024)-($_.UsedSpace/1024)) / ($_.Size/1024))}}
Write-Host "Table Information for $dbname" -ForegroundColor red
$tables | Select @{Label="DateCaptured";Expression={Get-Date -Format yyyyMMdd-HHmm}}, Name, RowCount, HasClusteredIndex |
Sort-Object RowCount -Descending
Write-Host "Table Indexes Information for $dbname" -ForegroundColor Red
$indexes | Select Parent, Name, IndexKeyType, IndexedColumns, FillFactor, SortInTempdb
}
Best Answer
I did something similar at a previous job and we ended up defining tables on my central server and granting permissions to my (monitored) sql sever agent service accounts to those tables. Then we created a job and linked server on each monitored instance to the central server.