Get properties of the objects that in multiple SQL Server instances

Introduction

This Windows PowerShell sample script helps you to get the properties of the objects in multiple SQL Server instances or SQL Server groups. It applies to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.

Scenarios

IT administrators frequently ask this question in the TechNet forums: how to get all logins/databases/agent jobs and their properties of multiple SQL Server instances? In traditional ways, we can write T-SQL query and obtain the expected results one by one. By using the following script, you can resolve the problems in below scenarios:

The objects belong to different instances or SQL Server group.
For different object, you would like to obtain the different properties.
Output the results in individual files for each object.

Script

Assuming there are two rows in my E:\Onescript\AllServers.txt file.
Server1\instancename,DataBases,Name|Owner
Server2,Logins,Name|LoginType
Customer can use this script by following below steps:

Step1: Launch the Windows PowerShell. Click Start, point to All Programs, point to Accessories, point to Windows PowerShell, right-click Windows PowerShell, and select Run As Administrator.
Screenshot:

Step2: Locate to the script path and load the script into current session.
Command: . E:\Onescript\PropertiesOfObjectsInMultipleInstances.ps1
Screenshot:

Note: Replace the E:\Onescript\ with your path.

Step3: Call the advanced function Get-OSCProperties.
Command: Get-OSCProperties
Screenshot:

Step4: The screen will prompt you enter in the path of the txt file that contains all the instances you would like to obtain their properties.
Command: E:\AllServers.txt
Screenshot:

Step5: The screen will prompt you enter in the output file path.
Command: E:\Output\
Screenshot:

Note: please enter with the correct format.

Step6: Check the output files in the output folder.

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

SQL

Edit|Remove

	Try
	{
  		$arrayRows=Get-Content $ServerListFileFullPath

  		#Check if the .txt file just has one row
  		$DataType=$arrayRows.GetType()|select Name
  		If ($DataType -match "String")
  		{
  			$i=$arrayRows.Length-1
  		}
  		else
  		{
   			$i=0
  		}
	}
	catch [System.Net.WebException],[System.IO.IOException]
	{
   		$error[0].InvocationInfo
	}

    Try 
    { 
          $arrayRows=Get-Content $ServerListFileFullPath 
 
          #Check if the .txt file just has one row 
          $DataType=$arrayRows.GetType()|select Name 
          If ($DataType -match "String") 
          { 
              $i=$arrayRows.Length-1 
          } 
          else 
          { 
               $i=0 
          } 
    } 
    catch [System.Net.WebException],[System.IO.IOException] 
    { 
           $error[0].InvocationInfo 
    }

Additional Resources

Using the SQL Server PowerShell Provider
Running Windows PowerShell Scripts