Iterating through SQL Servers and database objects with PowerShell

By: Diana Moldovan   |   Comments (5)   |   Related: > 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.

About the author

Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips