How do you use SQL Server PowerShell provider?

The following post looks at SQL Server 2012 instance on a Windows 8 Professional Computer.

SQL Server PowerShell provider enables you easily and interactively look at all objects much like you look at all the objects in a folder/file system.  You can use the command line to traverse paths in a SQL Server.

The server objects available as folders when you use Windows Power Shell are:
SQL
SQL Policy
SQL Registration
Utility
DAC
Data Collection
SQL Server Integration Services
SQL Server Analysis Services

In order to acces objects on SQL Server you need to run SQLPS.exe which you find in the following directory:
---------------------------------------------
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules>dir
 Volume in drive C is TI10672700E
 Volume Serial Number is 16FB-D230
 Directory of C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\M
odules

 

07/06/2014  06:33 PM    <DIR>          .
07/06/2014  06:33 PM    <DIR>          ..
07/06/2014  06:37 PM    <DIR>          SQLASCMDLETS
07/06/2014  06:40 PM    <DIR>          SQLPS
               0 File(s)              0 bytes
               4 Dir(s)  880,764,026,880 bytes free
----------------------
When you run SQLPS by double-clicking the file you spawn a new scripting shell 

------------
Microsoft SQL Server PowerShell
Version 11.0.2100.60
Microsoft Corp. All rights reserved.
which you can use with SQL Server as shown:
PS SQLSERVER:\>
--------------
In this shell you can access the previously mentioned SQL Objects much like you would acces folders/files using you DOS commands as shown here.
---------------
PS SQLSERVER:\> cd SQL
PS SQLSERVER:\SQL> cd..
PS SQLSERVER:\> cd SQLPolicy
PS SQLSERVER:\SQLPolicy> cd..
PS SQLSERVER:\> cd SQLRegistration
PS SQLSERVER:\SQLRegistration> cd..
PS SQLSERVER:\> cd Utility

How do we access the Server databases?
In order to do this you have to know the SQL Server Instance(s) and then the objects can be accessed as shown here:
-------
Get the instance name:
--------------------------
PS SQLSERVER:\> cd SQL
PS SQLSERVER:\SQL> dir
MachineName
-----------
HODENTEK8
localhost

PS SQLSERVER:\SQL> cd LocalHost
PS SQLSERVER:\SQL\LocalHost> dir
Instance Name
-------------
REGENCYPARK
------------------------------
Let us say we want all the objects on the AdventureWorks 2012 database on this instance.
You can find them as shown here:
--------------
PS SQLSERVER:\SQL\LocalHost\RegencyPark\Databases\AdventureWorks2012>
ApplicationRoles
Assemblies
AsymmetricKeys
Certificates
DatabaseAuditSpecifications
Defaults
ExtendedProperties
ExtendedStoredProcedures
Federations
FileGroups
FullTextCatalogs
FullTextStopLists
LogFiles
PartitionFunctions
PartitionSchemes
PlanGuides
Roles
Rules
Schemas
SearchPropertyLists
Sequences
ServiceBroker
StoredProcedures
SymmetricKeys
Synonyms
Tables
Triggers
UserDefinedAggregates
UserDefinedDataTypes
UserDefinedFunctions
UserDefinedTableTypes
UserDefinedTypes
Users
Views
XmlSchemaCollections

That's all for now.

Mahalo

Accessing PowerShell in SSMS go here:
http://hodentekmsss.blogspot.com/2014/04/starting-power-shell-from-sql-server.html