Powershell help

Question

Powershell help

vsamantha35

SSChampion

Points: 11551
More actions
October 25, 2016 at 11:08 am

#400935

Hi Experts,
Is there is powershell script which displays top 10 database tables taking up more space in each database on that sql instance in a nice HTML table format. I am not an powershell expert and so seeking for help. Checking if someone can help on this.
Also, if we can execute it against multiple sql instance it will be even more usefull.
Actual requirement.
Here is my base query and this has to be executed against all databases across multiple servers and send me an email in an HTML format.
SELECT DISTINCT TOP 10 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 [TotalSize MB]
, CAST((au.total_pages * 8/(1024.*1024)) AS numeric(15,2)) AS [TotalSize GB]
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 [TotalSize MB] DESC
Thanks in advance.
Sam

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 1

I don't have exactly what you need, but I have several pieces of code, that do some of these. For example, I have code to format to html and send email. But in the reports I do that, I use SMO, and don't execute SQL to html. I have other code, where I execute SQL, and use it for other purposes. Putting them together, I get the below code, but this is just an example, and in no way was it tested. In the $message.Body, you will need to update the Select with your information.

$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 [TotalSize MB]

, CAST((au.total_pages * 8/(1024.*1024)) AS numeric(15,2)) AS [TotalSize GB]

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 [TotalSize MB] DESC

"@

$InstanceList = "Instance1","Instance2","Instance..."

FOREACH ($InstanceName in $InstanceList)

{

Try

{

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

$DBList = $Instance.Databases

# Loop

ForEach ($DB in $DBList)

{

$ds = $DB.ExecuteWithResults($SQLText)

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

{

$RowData += $InstanceNameRow.Item(0)

}

catch

{

}

$a = "<style>"

$a = $a + "<!-- "

$a = $a + " TD{font-family: Arial; font-size: 8pt;} "

$a = $a + "--->"

$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>"

$smtpServer = "smtpServer.yourcompany.com"

$smtpFrom = "FromEmail@yourcompany.com"

$smtpTo = "ToEmail@yourcompany.com"

$messageSubject = "My Subject"

$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto

$message.Subject = $messageSubject

$message.IsBodyHTML = $true

$message.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

$smtp = New-Object Net.Mail.SmtpClient($smtpServer)

$smtp.Send($message)

Leonard

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 2

Attached is code that I use to get missing backups, and email the report. This may give you some additional ideas.

Leonard

vsamantha35 SSChampion Points: 11551 More actions · Answer 3

Thanks you very much for the help . Will check Leonard.

vsamantha35 SSChampion Points: 11551 More actions · Answer 4

Hi Leonard,

I dont know why but the script is getting executed but the data is not showing up. PFA screenshot.

Could you please help me in fixing it.

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 5

As I said, this was just some code I threw together, and didn't test. You need to add this:

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

That command is actually being deprecated, but I don't like the new way its being done. YMMV.

Having said that, I have attached a file, that I did test. Update $InstanceList with one or more instances.

There are many other ways this can be done, this is just one of them. Someone else may be able to provide you with better\faster code.

If you are not familiar with PowerShell, then the # represents a comment. A <# comment block #> is a comment block. As you can see, I have commented out the email send and the $RowData. if you run as is, the script will save a file to the Out-File, on the #Output to HTML File. Update to proper directory and file name. That will show you the output. When you want to run this, and send an email, comment out the HTML file output by putting a # in front of the line.

Remove the <# ># pairing, and update to your email values. The message body should be ready to go.

Leonard

vsamantha35 SSChampion Points: 11551 More actions · Answer 6

I slightly modified the catch block to display the exception.

catch [Exception]

{

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

}

Then as suspected it was throwing below runtime error

New-Object : Cannot find type [Microsoft.SqlServer.Management.SMO.Server]: make sure

the assembly containing this type is loaded.

I fixed it by using below command

Add-Type -Path 'C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'

Next it was throwing below error message :

You cannot call a method on a null-valued expression..Exception.GetType().FullName, You cannot

call a method on a null-valued expression..Exception.Message

vsamantha35 SSChampion Points: 11551 More actions · Answer 7

Hi Leonard,

Now everything is working fine. Email is being sent to the recipient but the format is not getting display in proper HTML format.

But if I go to the dir and open the html file in browser works perfectly fine. PFA latest screenshot.

Any thoughts??

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)

vsamantha35 SSChampion Points: 11551 More actions · Answer 8

Finally was able to fix the issue. Added below 3 lines and it worked like a charm. Thank you so much for the continous help Leonard.

$message = New-Object Net.Mail.MailMessage($EmailFrom, $EmailTo, $Subject, $body)

$message.IsBodyHtml = $true;

$SMTPClient.Send($message)

Regards,

Sam

mrrobsewell SSC-Addicted Points: 496 More actions · Answer 9

Hi,

This is how I would achieve this in straight SMO

Rob

# Load the assembly

[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );

foreach($Server in $Servers)

{

## Create a Server SMO Object

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server

## Define some expressions for data

$Mb = @{Name='TotalSizeMb';Expression = {[math]::Round(($_.DataSpaceUsed /1024) + ($_.IndexSpaceUsed/1024),2)}}

$GB = @{Name='TotalSizeGb';Expression = {[math]::Round((($_.DataSpaceUsed /1024) + ($_.IndexSpaceUsed/1024))/1024,2)}}

$IndexType = @{Name = 'IndexType';Expression = {if($_.HasClusteredIndex -eq 'True'){'Clustered'}else{'Heap'}}}

$ServerName = @{Name='ServerName';Expression = {$_.Urn.Parent.Value.Split("'")[1]}}

## Create an Array for results

$Results = @()

Foreach($db in $srv.Databases.Where{$_.IsAccessible -eq $True }) ## you could also add -and $_.IsSystemObject -eq $false to remove system dbs

{

$Results += $db.Tables |Sort-Object -Property @{Expression = {$_.DataSpaceUsed + $_.IndexSpaceUsed };Ascending = $False}|Select $ServerName,Parent,Schema,Name,HasCompressedPartitions,$IndexType,RowCount,DataSpaceUsed,$Mb,$GB -First 10

}

## Convert results into HTML and then to string for Send-MailMessage

$Body = $Results | ConvertTo-Html |Out-String

#Define Email variables

$smtpServer = ""

$From = ""

$To = ""

$Subject = ""

## Send Email

Send-MailMessage -To $To -Subject $Subject –From $From -SmtpServer $smtpServer -Body $Body –BodyAsHtml

https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 10

Yep. That's how I collect data to monitor db growth, get missing backups etc. No need to execute SQL in many cases.

Leonard

mrrobsewell SSC-Addicted Points: 496 More actions · Answer 11

Awesome, have you considered joining the SQL Community Collaborative on Github?

https://github.com/sqlcollaborative

or in the dbatools dbareports channel in the SQL Community Slack

That's what we do 🙂

https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]

vsamantha35 SSChampion Points: 11551 More actions · Answer 12

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