Hello,
The following script displays the size of SQL Server databases. However, some data is displayed as asterisks (*) instead of the full number. I’m assuming it’s because the length of the column is too small. Any ideas on how I can adjust the length?
[pre]
#Function Get-SpacedUsed {
[CmdletBinding()]
Param(
# the mandatory argument indicates that the parameter is required.
[Parameter(Mandatory=$true)]
[String] $inputfilename
)
[string] $username = ‘’
[string] $password = ‘’
# the variable to build connection, command, adapter, and dataset
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
# defining object to use sql commands
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
# dataset to store the adapter result
$dataset1 = New-Object System.Data.DataSet
$dataset2 = New-Object System.Data.DataSet
# store the output of PSCustomObject to a hash table
$object = @()
# loop through each server listed in inputfile
foreach ($srv in get-content $inputfilename) {
# reset the dataset
$dataset1.Reset()
$dataset2.Reset()
# build the connection string
#$sqlConn.ConnectionString = “Server=$srv;Database=master;IntegratedSecurity=True”
#$sqlConn.ConnectionString = “Server=$srv;Database=master;User ID=$username;Password=$password;”
$sqlConn.ConnectionString = “Server=$srv;Database=master;User ID=$username;password=$password;”
# fetch database name from sys.databases table and ignore system database
$sqlCmd.CommandText = “SELECT name FROM master.sys.databases WHERE database_id > 4”
# setup the sql connection using sqlclient namespace
$sqlCmd.Connection = $sqlConn
# fill the datasets based on the data we are extracting
$sqlAdapter.SelectCommand = $sqlCmd
# SqlDataAdapter serves as a bridge between a dataset and SQL Server for
# retrieving and saving data
$sqlAdapter.Fill($dataset1) | Out-Null
# assign dataset1 to a variable and referring the indicator to start at 0
$dbs = $dataset1.Tables[0]
# loop through the listed databases
foreach ($db in $dbs) {
# prepare the sql statement by appending the database name to stored procedure
$sqlCmd.CommandText = $db.name+"…sp_spaceused "
# setup the sql connection
$sqlCmd.Connection = $sqlConn
# assign the query to an interface to retrieve data
$sqlAdapter.SelectCommand = $sqlCmd
# fill dataset; create an in-memory cache of a data table that can easily be manipulated
$sqlAdapter.Fill($dataset2) | Out-Null
}
# close the connection
$sqlConn.Close()
# the below code merges the resultset of two datasets
for($i = 0; $i -lt $dataset2.Tables[0].Rows.Count; $i++){
$object += New-Object PSObject -Property @{
“ServerName” = $srv
“DatabaseName” = $($dataset2.Tables[0].Rows[$i][0])
“DatabaseSize” = $($dataset2.Tables[0].Rows[$i][1])
“UnallocatedSpace” = $($dataset2.Tables[0].Rows[$i][2])
“Reserved” = $($dataset2.Tables[1].Rows[$i][0])
“Data” = $($dataset2.Tables[1].Rows[$i][1])
“IndexSize” = $($dataset2.Tables[1].Rows[$i][2])
“Unused” = $($dataset2.Tables[1].Rows[$i][3])
}
}
}
# selecting the attributes to console
$object | Select-Object ServerName, DatabaseName, DatabaseSize, UnallocatedSpace, Reserved, Data, IndexSize, Unused | Format-Table -AutoSize
$json = $object | Select-Object ServerName, DatabaseName, DatabaseSize, UnallocatedSpace, Reserved, Data, IndexSize, Unused | ConvertTo-Json
#region
#--------------------------------------------------------------------
$dateString = (Get-Date).ToString(“MMddyyyy”)
$filePath = "C:\Temp\sqlstorage\output"
$fileName = ($filePath + “sqlgrowthoutput-” + $dateString + “.json”)
#endregion
# write the output to file
$json | Out-File $fileName
# invoke process to open JSON file
#Invoke-Item $fileName
#} # end Get-SpaceUsed
[/pre]
Thanks!