Powershell and SQL Server – 3- Access SQL Server Instance – List Databases and Properties with Invoke-Sqlcmd

List out database name from a SQL Instance

if (-not (Get-Module SQLPS)) 
    {
    Import-Module SQLPS
    } 

$SqlServerName="LENOVO-PC\SQL2016"

$result=Invoke-Sqlcmd -ServerInstance $SqlServerName  -Database master -Query "SELECT * FROM sys.sysdatabases" -QueryTimeout 0
 
 foreach ($dbname  in $result.name)
 {
    $dbname
 }

or 
 foreach ($dbname in $result)
 {
 $dbname.name
 }

ListDatabaseNamePS1

List Database properties for each database

if (-not (Get-Module SQLPS)) 
    {Import-Module SQLPS} 
$SqlServerName="LENOVO-PC\SQL2016"
$result=Invoke-Sqlcmd -ServerInstance $SqlServerName  -Database master -Query "SELECT * FROM sys.sysdatabases" -QueryTimeout 0 
 foreach ($dbname  in $result)
 {
    write-host $dbname.name 
    Write-host "---------------"
    $cmd="select * from sys.sysdatabases where name='"+$dbname.name+"'"
    $dbprop=Invoke-Sqlcmd -ServerInstance $SqlServerName  -Database master -Query $cmd -QueryTimeout 0
    $dbprop
 }

DBProp1

Leave a Reply

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