Asterisks in output instead of numbers

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!

Assuming you’re referencing line 93:

$object | Select-Object ServerName, DatabaseName, DatabaseSize, UnallocatedSpace, Reserved, Data, IndexSize, Unused | Format-Table -AutoSize

Typically, if the data does not fit, it is represented by a elipsis (…), not an asterisk (*). Try Format-List instead. If you look at the JSON, the data is correct. The continuation only is indicated in console output, the full data should be there for ConvertTo-Json or other cmdlets