Thanks mrrobsewell & Leonard. Both solutions are good. thank you.

@mrrobsewell - I need a small help. Considering Leonard solution, how can format in such a way so that Report, should be as follows.

Also I wanted to put a background color with YELLOW for top 3 records for all databases for the column "Total Size MB". How can I do that? Could you please help?

Server1

Table grid with all data specific to that server

--// 1 or 2 Empty rows

Server2

Table grid with all data specific to that server

// 1 or 2 Empty rows

Server3

Table grid with all data specific to that server

Complete code I am using :

=========================

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

#clear

$InstanceList = @()

$RowData = @()

$SQLText = @"

SELECT DISTINCT TOP 10 @@SERVERNAME AS InstanceName,

DB_NAME() AS DatabaseName

,s.Name AS SchemaName

,object_name(i.object_id) AS TableName

, i.index_id AS IndexID

, i.type_desc AS IndexType

,p.data_compression_desc

, p.[rows] AS NumRows

, CAST((au.total_pages * 8/1024.) AS numeric(15,2)) AS TotalSizeMB

, CAST((au.total_pages * 8/(1024.*1024)) AS numeric(15,2)) AS TotalSizeGB

FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id

INNER JOIN sys.allocation_units au ON

CASE

WHEN au.[type] in (1,3) THEN p.hobt_id

WHEN au.type = 2 THEN p.partition_id

end = au.container_id

INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id

INNER JOIN [sys].[database_files] d ON f.[data_space_id] = d.[data_space_id]

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

Where t.type_desc ='USER_TABLE'

and i.index_id in (0,1) --- 0 -- heaps tbls , 1 -- clustered indexes

and au.total_pages > 0

and o.is_ms_shipped <> 1

ORDER BY TotalSizeMB DESC

"@

$InstanceList = "TESTSRV"

FOREACH ($InstanceName in $InstanceList)

{

Try

{

$Instance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName

$DBList = $Instance.Databases | Where-Object { !($_.IsSystemObject) }

# Loop

ForEach ($DB in $DBList)

{

$ds = $DB.ExecuteWithResults($SQLText)

Foreach ($Row in (($ds.Tables[0]).Rows))

{

$RowData += $Row | SELECT InstanceName, DatabaseName, SchemaName, TableName, IndexID, IndexType, data_compression_desc, NumRows, TotalSizeMB, TotalSizeGB

}

}

}

catch [Exception]

{

Write-Host "$_.Exception.GetType().FullName, $_.Exception.Message" -ForegroundColor Red

}

}

# Output to Write-Host

#$RowData

# Format for HTML

$a = "<style>"

$a = $a + "BODY{background-color:peachpuff;}"

$a = $a + "TABLE{font-family: Arial;font-size: 8pt;width:100%; height:75%; border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"

$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"

$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"

$a = $a + "</style>"

# Output to HTML File

$RowData | SELECT @{Name='Instance Name';Expression={$_.InstanceName}}, @{Name='Database Name';Expression={$_.DatabaseName}}, @{Name='Schema Name';Expression={$_.SchemaName}}, @{Name='Table Name';Expression={$_.TableName}}, @{Name='Index ID';Expression={$_.IndexID}}, @{Name='Index Type';Expression={$_.IndexType}}, @{Name='Data Compress Desc';Expression={$_.data_compression_desc}}, @{Name='Number of Rows';Expression={$_.NumRows}}, @{Name='Total Size MB';Expression={$_.TotalSizeMB}}, @{Name='Total Size GB';Expression={$_.TotalSizeGB}} | ConvertTo-HTML -head "<H2>Top Table List</H2>" -body $a | Out-File C:\Temp\TableList.html

$EmailFrom = "xxxxx@gmail.com"

$EmailTo = "zzzzz@gmail.com"

$Subject = "Top 10 table list "

$Body = get-content ("C:\Temp\TableList.html")

#$Body = $RowData | SELECT @{Name='Instance Name';Expression={$_.InstanceName}}, @{Name='Database Name';Expression={$_.DatabaseName}}, @{Name='Schema Name';Expression={$_.SchemaName}} | ConvertTo-HTML -head "<H2>Top Table List</H2>" -body $a

$SMTPServer = "smtp.gmail.com"

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)

$SMTPClient.EnableSsl = $true

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("xxxxx@gmail.com", "cpwdded#123");

$SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)

Thanks,

Sam