PowerShell: Simple SQL Command Example

Updated on 2/7/2015

Direct database queries and updates can be done using Windows PowerShell and the ADO.NET object framework. In the following example, we make use of the SqlConnection and SqlCommand classes to query the AllWebs tables of all Microsoft SharePoint content databases. The connection strings are created for each SPContentDatabase object returned from the Get-SPContentDatabase cmdlet. The following example also demonstrates how to create a table in Windows PowerShell starting with a null array (“@()”).

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
 
#loop over all the content databases
foreach($contentDB in Get-SPContentDatabase)
{	 
	Write-Host "Content DB: " $contentDB.Name
	 
	#create a connection string, and connect to SQL
	$sqlConnection.ConnectionString = "Data Source=" + $contentDB.Server + ";Initial Catalog=" + $contentDB.Name + ";Integrated Security=True;Enlist=False;Connect Timeout=15"
	$sqlConnection.Open()	 
	$sqlCommand.connection = $sqlConnection
	 
	#SQL statement to list webs
	$sqlCommand.CommandText = "SELECT title, fullurl, timecreated FROM AllWebs"
	$resultRows = $sqlCommand.ExecuteReader()
	 
	if($resultRows.HasRows -eq $true)
	{		
		#create a generic PowerShell object for storing rows of a table
		$outputTable = @()
	    	
		#read all SQL results and store in a table
		while($resultRows.Read())
		{
			#create a row and add it to the output table
			$outputRow = new-object psobject
	    		$outputRow | add-member noteproperty Title $resultRows[0]
	    		$outputRow | add-member noteproperty FullUrl $resultRows[1]
	    		$outputRow | add-member noteproperty TimeCreated $resultRows[2]					
			$outputTable = $outputTable + $outputRow
		}
		
		#output the table to the user
		$outputTable | ft
	}
	else
	{
		Write-Host "No webs found in AllWebs"
	}
	$resultRows.Close()
	$sqlConnection.Close()
} 

Output


Content DB:  WSS_Content

Title                      FullUrl                      TimeCreated                                 
-----                      -------                      -----------                                 
Home                                                    1/16/2014 4:14:47 PM                        
Search                     search/center                1/18/2014 8:50:34 PM                        
My Site Host               my/host                      1/18/2014 8:48:26 PM