Free SQL Help for Devs, DBAs, and the Curious

Maintaining a comprehensive and up-to-date inventory of SQL Server databases is essential for effective management and monitoring of your database environment. Whether you're an administrator of a small database ecosystem or a DBA managing multiple database platforms (SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, Azure SQL), documenting your SQL Server inventory can be a time-consuming task. Thankfully, PowerShell can simplify this task by automating the process and ensuring the database inventory is always current and accurate.

Start by installing the SQL Server module for PowerShell. This module, called "sqlps", provides a rich set of SQL Server-specific cmdlets, allowing you to interact with SQL Server instances without even leaving the command line. To install the sqlps module, simply open PowerShell and run the "Install-Module sqlps" command. Note that you need to have PowerShell 5.0 or newer installed to use the Install-Module command.

Once you've installed the sqlps module, you can use its cmdlets to retrieve information about your SQL Server instances. For instance, the "Get-SqlInstance" cmdlet allows you to retrieve a list of all SQL Server instances in your environment. Here's a sample usage of this cmdlet:

Import-Module sqlps
Get-SqlInstance -ServerInstance "YourServerName"

This will return a list of all SQL Server instances hosted on the specified server. The list includes the instance name, version, edition, and other useful information. You can also use the "Get-SqlDatabase" cmdlet to retrieve a list of all databases hosted on a given SQL Server instance:

Get-SqlDatabase -ServerInstance "YourServerName"

This cmdlet returns a list of all databases on the specified SQL Server instance, including the database name, size, recovery model, compatibility level, and other useful information. Combined, these cmdlets provide a powerful tool for documenting your SQL Server inventory.

Of course, PowerShell is not limited to SQL Server. Thanks to its flexibility and extensibility, you can use PowerShell to document your MySQL, DB2, and Azure SQL databases as well. For instance, to retrieve a list of all MySQL databases, you can use the MySQL .NET Connector to connect to your MySQL server and execute a "SHOW DATABASES" command:

$connectionString = "server=YourServerName;userid=YourUserId;password=YourPassword;database=mysql"
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = "SHOW DATABASES"
$reader = $command.ExecuteReader()
while ($reader.Read()) {
    $reader.GetString(0)
}
$connection.Close()

In conclusion, PowerShell provides a powerful and flexible tool for documenting your SQL Server, MySQL, DB2, and Azure SQL databases. By automating the process, you can ensure that your database inventory is always up-to-date and accurate, freeing you up to focus on more important tasks.