Iterating through SQL Servers and database objects with PowerShell

Problem

I’ve read several articles on MSSQLTips about executing queries against multiple servers or databases. I found advice about using the Local Servers Group to run a query against more than one SQL instance, about Central Management Server, about sp_MSforeachdb or about using the Management Studio query editor in SQLCMD mode. What about PowerShell? Can I use PowerShell to run queries against several instances, databases and database objects?

Solution

Yes.  Let’s work through some examples.  I’ve run the examples I’m presenting here in an environment consisting of 3 servers grouped in a domain. Each server hosts a single SQL Server instance – there is a SQL 2008 instance, a SQL 2005 instance and a SQL 2008 R2 instance. I’m connecting to these servers using a domain account which is in sysadmin role on all the machines. In order to set up PowerShell for working with SQL Server, I’ve added the SqlServerCmdletSnapin100 and SqlServerProviderSnapin100 cmdlet’s to my PowerShell profile.

Let’s “translate” into PowerShell the examples I’ve set up for you here.

Collecting information about the SQL Servers in your network

    $Servers = “SQL2005”, “SQL2008”, “SQL2008R2”

    $Query = “SELECT SERVERPROPERTY(‘ServerName’) AS ServerName

                ,SERVERPROPERTY(‘ProductVersion’) AS ProductVersion

                ,SERVERPROPERTY(‘ProductLevel’) AS ProductLevel

                ,SERVERPROPERTY(‘Edition’) AS Edition

                ,SERVERPROPERTY(‘EngineEdition’) AS EngineEdition;”

    $Servers | ForEach-Object{

 $server = “$_”;

 Set-Location SQLSERVER:\SQL\$server Invoke-Sqlcmd -Query $Query -ServerInstance $server;

 }

    

I’m using the Invoke-Sqlcmd cmdlet and ForEach-Object to run the query against each server from the list. Instead of reading each server from the list, you can provide a text input file which contains all the server names and read the content of that file. Here is the expected result:

     ServerName     : SQL2005

    ProductVersion : 9.00.5000.00

    ProductLevel   : SP4

    Edition        : Enterprise Edition

    EngineEdition  : 3

    ServerName     : SQL2008

    ProductVersion : 10.0.4000.0

    ProductLevel   : SP2

    Edition        : Developer Edition

    EngineEdition  : 3

    ServerName     : SQL2008R2

    ProductVersion : 10.50.1600.1

    ProductLevel   : RTM

    Edition        : Enterprise Edition (64-bit)

    EngineEdition  : 3

    

Disabling the SQL Server guest user in each database for each server

This time the query will come from an input file – here is its content:

    REVOKE CONNECT FROM GUEST

   GO

    

The script will be executed against each database of each SQL instance.

    $Servers = “SQL2005”, “SQL2008”, “SQL2008R2”

    $InputFile = “C:\MyLocation\RevokeGuest.sql”;

    $Servers | ForEach-Object{

 $server = “$_”;

 Set-Location SQLSERVER:\SQL\$server\DEFAULT\DATABASES;

 Get-ChildItem | ForEach-Object{

 $Db = $_.Name;

 Invoke-Sqlcmd -SuppressProviderContextWarning -InputFile $InputFile -Database $Db;

     }

    }

    

Use the “SuppressProviderContextWarning” parameter to remove the warning message “WARNING: Using provider context. Server = MyServer”. Otherwise this message will appear on your screen, once for every database processed.

Collecting information about the databases from a SQL Server instance

Over time, you’ll add complexity to your admin and maintenance scripts. However, the “core” of the script may be straightforward. The next example shows you how easy will be to collect information about the databases from a SQL instance and to export this information in a csv file:

    $OutFile = “C:\MyLocation\OutFile.csv”

    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES

    Get-ChildItem | Select-Object Name, Status | Export-csv -NoTypeInformation $OutFile

    

I’m using the “NoTypeInformation” parameter so that the .NET object type won’t be mentioned in the output file.

Next Steps

• As you have a need to address problems in your environment, consider PowerShell as an alternative.
• Check out all of the PowerShell tips on MSSQLTips.

Diana Moldovan

Diana is currently working as a DBA and data centric applications developer for a small ISV company. Her 6 years of experience covers SQL 2000, SQL 2005 and SQL 2008. Besides working with SQL Server, she also does .NET development. Diane is constantly trying to improve her knowledge; therefore, she is involved as much as possible in the activity of the local PASS chapter.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2020