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>