Introduction to ChatGPT and PowerShell

In this article, we will show how to work with ChatGPT and PowerShell. In previous articles, we chat with ChatGPT, which is an extremely powerful language model with advanced AI, able to do a lot of tasks and code. We also show some practical examples in SQL Server, especially in T-SQL.

In this article, we will work with PowerShell and ChatGPT. PowerShell is a Powerful command-line shell used to automate and integrate several tools. This article will work with PowerShell, SQL Server, and ChatGPT.

ChatGPT is a large language model developed by OpenAI, designed to generate human-like responses to text-based inputs. It uses deep learning techniques to understand the context and meaning of natural language text, allowing it to generate coherent and relevant responses to a wide range of questions and prompts.

Can you generate a sample in PowerShell in SQL Server?

ChatGPT: Here's an example of a PowerShell script that performs a full database backup in SQL Server:

# Set the database name
$databaseName = "MyDatabase"
# Set the backup directory
$backupDirectory = "C:\Backups"
# Create the backup filename
$backupFilename = $databaseName + "_" + (Get-Date).ToString('yyyyMMddHHmmss') + ".bak"
# Create the backup path
$backupPath = $backupDirectory + "\" + $backupFilename
# Perform the backup
Backup-SqlDatabase -ServerInstance "localhost" -Database $databaseName -BackupFile $backupPath -CompressionOption On

In this example, the script sets the database name and backup directory, creates a unique backup filename based on the current date and time, and then performs the backup using the Backup-SqlDatabase cmdlet. The -ServerInstance parameter specifies the name of the SQL Server instance, and the -Database parameter specifies the name of the database to backup. The -BackupFile parameter specifies the path and filename of the backup file to create, and the -CompressionOption parameter specifies whether to compress the backup or not.

Me: When we run the code, we got the following error message:

Backup-SqlDatabase : System.Data.SqlClient.SqlError: Database 'MyDatabase' does not exist. Make sure that the name is entered correctly.
At line:14 char:1
+ Backup-SqlDatabase -ServerInstance "localhost" -Database $databaseNam ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Backup-SqlDatabase], SmoException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

The database does not exist. To fix the problem I changed the database name:

# Set the database name
$databaseName = "adventureworks2019"
# Set the backup directory
$backupDirectory = "C:\Backups"
# Create the backup filename
$backupFilename = $databaseName + "_" + (Get-Date).ToString('yyyyMMddHHmmss') + ".bak"
# Create the backup path
$backupPath = $backupDirectory + "\" + $backupFilename
# Perform the backup
Backup-SqlDatabase -ServerInstance "localhost" -Database $databaseName -BackupFile $backupPath -CompressionOption On

Finally, I verified the backup and it was created successfully:

Can you generate a sample to work with Windows Services?

ChatGPT: Yes. Another useful feature of PowerShell in SQL Server is the ability to manage SQL Server instances. With PowerShell, you can start, stop, and restart SQL Server services, view and modify SQL Server configurations, and even create and manage SQL Server logins and users.

Here's an example of a PowerShell script that stops the SQL Server service:

# Stop the SQL Server service
Stop-Service MSSQLSERVER

In this example, the script uses the Stop-Service cmdlet to stop the SQL Server service. You can also use the Start-Service and Restart-Service cmdlets to start and restart the SQL Server service, respectively.

Me: Let´s try something else.

Can you generate a script to stop the SQL Service if the CPU usage is over 90?

Yes, here's a PowerShell script that stops the SQL Server service if the CPU usage is over 90

$serverName = "localhost"
$cpuThreshold = 90
# Get the current CPU usage for the SQL Server process
$process = Get-Process -ComputerName $serverName -Name sqlservr
$cpuUsage = ($process.CPU / (Get-WmiObject -Class win32_processor -ComputerName $serverName).NumberOfLogicalProcessors) * 100
if ($cpuUsage -gt $cpuThreshold) {
# Stop the SQL Server service
Stop-Service MSSQLSERVER
}

In this script, the first line sets the name of the SQL Server instance that you want to monitor. The second line sets the CPU usage threshold to 90.

The script then gets the current CPU usage for the SQL Server process using the Get-Process cmdlet and calculates the percentage of CPU usage based on the number of logical processors using the Get-WmiObject cmdlet. If the CPU usage is greater than the threshold value, the script stops the SQL Server service using the Stop-Service cmdlet.

You can save this script as a .ps1 file and schedule it to run periodically using the Windows Task Scheduler. This will help you ensure that your SQL Server instance is not overloaded with CPU usage and prevent performance issues.

Me: What I love about PowerShell is that you easily check Disks, and memory and take some actions. Note that it is not a best practice to stop the SQL Service at 90 This is just an example for didactic purposes.

Can you generate a PS script to detect which table in my database adventureworks2019 is consuming more CPU?

ChatGPT: Yes, here's a PowerShell script that detects which table in the AdventureWorks2019 database is consuming more CPU:

# Set the database name
$databaseName = "AdventureWorks2019"
# Get the SQL Server instance
$instance = New-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
# Get the database object
$database = $instance.Databases[$databaseName]
# Get the list of tables
$tables = $database.Tables
# Loop through each table
foreach ($table in $tables) {
# Get the CPU time for the table
$cpuTime = $table.ExecutionManagerStatistics.CpuTime
# Output the table name and CPU time
Write-Output "$($table.Schema).$($table.Name): $cpuTime"
}
# Get the table with the highest CPU time
$highestCpuTable = $tables | Sort-Object -Property ExecutionManagerStatistics.CpuTime -Descending | Select-Object -First 1
# Output the table with the highest CPU time
Write-Output "Table with highest CPU time: $($highestCpuTable.Schema).$($highestCpuTable.Name)"

In this script, the first line sets the database name to AdventureWorks2019. The script then gets the SQL Server instance using Microsoft.SqlServer.Management.Smo.Server class and gets the database object using the $instance.Databases property. The script then loops through each table in the database and gets the CPU time for each table using ExecutionManagerStatistics.CpuTime property. The table name and CPU time are output to the console using the Write-Output cmdlet. After the loop, the script uses the Sort-Object cmdlet to sort the list of tables by CPU time in descending order and selects the table with the highest CPU time using the Select-Object cmdlet. The table name with the highest CPU time is output to the console.

You can save this script as a .ps1 file and run it using PowerShell. The script will output the CPU time for each table in the AdventureWorks2019 database and the table with the highest CPU time. This will help you identify which table is consuming the most CPU and take necessary actions to optimize the query performance.

Me: I really love the power of PowerShell. You can easily combine SMO with CpuTime statistics. Here is the output:

Tables and cpu usage

Can you write a PowerShell script in my local SQL Server in the Adventureworks2019 database and add the last 5 modified tables in a csv file?

ChatGPT: Sure, here's a PowerShell script that will get the last 5 modified tables in the Adventureworks2019 database and save them to a CSV file:

# Connect to the Adventureworks2019 database
$server = "localhost"
$database = "Adventureworks2019"
$connString = "Server=$server;Database=$database;Integrated Security=True;"
$conn = New-Object System.Data.SqlClient.SqlConnection($connString)
$conn.Open()
# Get the last 5 modified tables
$sql = "SELECT TOP 5 name, modify_date FROM sys.tables ORDER BY modify_date DESC"
$cmd = New-Object System.Data.SqlClient.SqlCommand($sql, $conn)
$reader = $cmd.ExecuteReader()
# Save the results to a CSV file
$results = @()
while ($reader.Read()) {
$table = New-Object PSObject -Property @{
Name = $reader["name"]
ModifiedDate = $reader["modify_date"]
}
$results += $table
}
$results | Export-Csv -Path "C:\path\to\file.csv" -NoTypeInformation
# Close the connection
$conn.Close()

Replace "localhost" with the name of your SQL Server if it's not running on your local machine. Also, replace "C:\path\to\file.csv" with the actual path where you want to save the CSV file.

Me: When I run the code, I got the following error message:

Export-Csv : Could not find a part of the path 'C:\path\to\file.csv'.
At line:22 char:12
+ $results | Export-Csv -Path "C:\path\to\file.csv" -NoTypeInformation
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Export-Csv], DirectoryNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand

So, I changed the path C:\path\to\file.csv to C:\data\file.csv. The C:\data path exists on my PC.

I run the script again and voilà it worked:

Last modified table in SQL Server

This task is 100 times easier in T-SQL but I just wanted to show that it was possible in PowerShell as well.

Can you create a PowerShell script to detect the 5 tables of the adventureworks2019 SQL Server database using more disk space and send me the list in a txt file named tablediskspace.txt in the c:\data path?

ChatGPT: Certainly! Here's a modified version of the previous PowerShell script that will save the output file to the C:\data path:

# Set up database connection parameters
$serverName = "localhost"
$databaseName = "AdventureWorks2019"
$tableCount = 5
$outputFile = "C:\data\tablediskspace.txt"
# Create a SQL Server object and connect to the database
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$database = $server.Databases[$databaseName]
# Get the top N tables by size
$tables = $database.Tables | Sort-Object -Descending -Property DataSpaceUsed | Select-Object -First $tableCount
# Format the table data for output
$output = $tables | Select-Object Name, @{Name="DataSpaceUsedMB";Expression={"{0:N2}" -f ($_.DataSpaceUsed / 1MB)}} | ConvertTo-Csv -NoTypeInformation
# Save the output to a file
$output | Out-File $outputFile

You'll need to update the $serverName, $databaseName, $tableCount, and $outputFile variables to match your environment and preferences. The script will retrieve the top 5 tables by size from the AdventureWorks2019 database, format the data as a CSV, and save it to a text file named tablediskspace.txt. The file will be created in the C:\data path.

Once you've updated the script, save it to a file with a .ps1 extension, open PowerShell, navigate to the directory where the file is located, and run the script by typing its filename (e.g., .\MyScript.ps1) and pressing Enter. The script will create the text file in the C:\data path and save the table data in it.

Me: Something that will happen is that if you are not familiar with the shell, or programming language, it will be hard to fix the problem. ChatGPT can guide you to fix the problem and error, but it does not always know the correct answer to fix the error. It is still required to have experience in the language to fix problems. ChatGPT code will not always compile perfect code. However, with the Microsoft alliance, this will improve soon.

Conclusion about ChatGPT and PowerShell

Using a simple chat from ChatGPT can generate interesting code. PowerShell + ChatGPT can automate several tasks in a few minutes. However, the ChatGPT version in the browser is the simplest version. In future chapters, we will talk about the Extensions in Visual Studio to connect to a more specialized version of ChatGPT oriented to Developers.

The chat version in the browser is very interesting. However, if it makes mistakes, it is hard for a newbie to fix the errors. However, ChatGPT knows how to solve several error messages and it may be possible, that in some cases, it will help you to solve the errors generated by itself.