Using PowerShell for SQL Server Admin

All SQL Server 2008 installations will automatically have PowerShell. As a SQL Server DBA you will not survive  without knowing PowerShell in future. For SQL Server there is a separate component of  PowerShel – SQLPS. You start SQLPS in SQL Server or by typing SQLPS in command prompt. SQLPS loads required components which you can work with SQL Server. In the normal PowerShell editor SQL Server related components are not loaded.

Starting SQLPS from SQL Server:

PowerShell for SQL Server


PowerShell for SQL Server

In SQLPS there is a directory structure which you can traverse through that using “cd” which is essentially an alias of Set-Location cmdlet. You can see the directories available in each level by executing “dir” which is another alias for the Get-ChildItem cmdlet.

PowerShell for SQL Server

In the root there are four logical drives or directories as you can see in the above screenshot.

  1. SQL
  2. SQLPolicy
  3. SQLRegistration
  4. DataCollection

The below screenshot shows that I have traversed through the directory structure upto “tables” object in my local server. Look at the prompt carefully.

PowerShell for SQL Server

Now if you execute “dir” you will get a list of tables available in the AdventureWorks database. You can execute T-SQL commands at this point. For example to get a list of the top 10 tables with the highest number of records.

gci | sort-object -Property RowCount -Desc | select-object schema,name -first 10 | format-table


Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>