Accessing SQL Entities in Powershell with SQLPS

I was playing with SQLPS for the first time and wanted to save my samples for future use.

Code

cls 
Write-Host "Start" 
Import-Module SQLPS –DisableNameChecking
Write-Host "Done with Import-Module" 
cd SQLSERVER:\
DIR

Write-Host "`n`nList of Instances (not working?)" 
cd SQLSERVER:\SQL\localhost 
Get-ChildItem | Select instancename  

Write-Host "`n`nList of Databases on Local Server default instance" 
cd SQLSERVER:\sql\localhost\DEFAULT\Databases   # specify "DEFAULT" if you have no instance name 
Get-ChildItem | Select name 

Write-Host "`n`nAlternate List of Databases on Local Server default instance" 
Invoke-SQLcmd -Server '.' -Database master 'select name, database_id, create_date from sys.databases' | Format-Table

Write-Host "`n`nList of tables in NealDemo Database" 
cd SQLSERVER:\sql\localhost\DEFAULT\Databases\NealDemo\Tables
Get-ChildItem 

Write-Host "`n`nRun some SQL Command" 
Invoke-Sqlcmd -Query "SELECT @@VERSION, db_name();"

#$sqlpath = "SQLSERVER:\sql\localhost\DEFAULT\Databases`nealDemo\Tables"
#dir
Write-Host "End" 

Output

Start
Done with Import-Module

Name            Root                           Description                             
----            ----                           -----------                             
DAC             SQLSERVER:\DAC                 SQL Server Data-Tier Application        
                                               Component                               
DataCollection  SQLSERVER:\DataCollection      SQL Server Data Collection              
SQLPolicy       SQLSERVER:\SQLPolicy           SQL Server Policy Management            
Utility         SQLSERVER:\Utility             SQL Server Utility                      
SQLRegistration SQLSERVER:\SQLRegistration     SQL Server Registrations                
SQL             SQLSERVER:\SQL                 SQL Server Database Engine              
SSIS            SQLSERVER:\SSIS                SQL Server Integration Services         
XEvent          SQLSERVER:\XEvent              SQL Server Extended Events              
DatabaseXEvent  SQLSERVER:\DatabaseXEvent      SQL Server Extended Events              
SQLAS           SQLSERVER:\SQLAS               SQL Server Analysis Services            

List of Instances

InstanceName : 

List of Databases on Local Server default instance

Name : EMPLOYEES

Name : NealDemo

Name : ReportServer

Name : ReportServerTempDB

Name : VideoGenerator

Alternate List of Databases on Local Server default instance
WARNING: Using provider context. Server = localhost.

name                                                                        database_id create_date                                
----                                                                        ----------- -----------                                
master                                                                                1 4/8/2003 9:13:36 AM                        
tempdb                                                                                2 4/16/2015 11:42:24 PM                      
model                                                                                 3 4/8/2003 9:13:36 AM                        
msdb                                                                                  4 2/20/2014 8:49:38 PM                       
ReportServer                                                                          5 12/11/2014 5:42:06 PM                      
ReportServerTempDB                                                                    6 12/11/2014 5:42:07 PM                      
NealDemo                                                                              7 1/8/2015 1:31:34 PM                        
VideoGenerator                                                                        8 1/12/2015 2:08:16 PM                       
EMPLOYEES                                                                             9 1/20/2015 2:21:54 PM                       

List of tables in NealDemo Database

Schema                       Name                           Created               
------                       ----                           -------               
Audit                        AuditAllExclusions             2/4/2015 8:21 AM      
Audit                        AuditBaseTables                2/4/2015 8:21 AM      
Audit                        AuditDetail                    2/4/2015 8:21 AM      
Audit                        AuditDetailArchive             2/4/2015 8:21 AM      
Audit                        AuditHeader                    2/4/2015 8:21 AM      
Audit                        AuditHeaderArchive             2/4/2015 8:21 AM      
Audit                        AuditSettings                  2/4/2015 8:21 AM      
Audit                        SchemaAudit                    2/4/2015 8:21 AM      
dbo                          Employee                       2/4/2015 3:55 PM      

Run some SQL Command
WARNING: Using provider context. Server = localhost, Database = NealDemo.

Column1 : Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
              Feb 20 2014 20:04:26 
              Copyright (c) Microsoft Corporation
              Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
          
Column2 : NealDemo

End

PS SQLSERVER:\sql\localhost\DEFAULT\Databases\NealDemo\Tables> 

Uncategorized  

Leave a Reply