none
Put variable into another variable RRS feed

  • Question

  • Hi All~

    I am writing the following script:

    Add-Type -Path 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll'

    get-content 'C:\SLDATA\Serverlist.txt'  | foreach-object {
    #write-output `n
    ###write "Server Name: $_"
    $SrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $SrvConn.ServerInstance=$_
    #Use Integrated Authentication
    $SrvConn.LoginSecure = $true
    $SrvConn.ConnectTimeout = 1
    $srv = new-object Microsoft.SqlServer.Management.SMO.Server($SrvConn)
    $dbs += $srv.Databases
    }
    $dbs | select name,onwer,size | Sort-object size
    clear-variable -Name dbs

    Then the output will be :

    Name               Owner              size

    master             sa                     1024

    master             sa                     2048

    tempdb             sa                    5000

    tempdb            sa                     6000

    if I want to add a output column of the server name like the following

    Servername     Name        Owner     Size

    Server1            master       sa           1024

    Server2            master        sa         2048

    Server1             master       sa         5000

    Server2            master        sa           6000

    how to re-write the script


    • Edited by sakurai_db Thursday, March 20, 2014 8:38 AM not yet complete at the first time
    Thursday, March 20, 2014 8:33 AM

All replies

  • Try this:

    Add-Type -Path 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll'
    
    $Servers = get-content 'C:\SLDATA\Serverlist.txt'
    foreach ($server in $servers) {
    #write-output `n
    ###write "Server Name: $_"
    $SrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $SrvConn.ServerInstance=$_
    #Use Integrated Authentication
    $SrvConn.LoginSecure = $true
    $SrvConn.ConnectTimeout = 1
    $srv = (new-object Microsoft.SqlServer.Management.SMO.Server($SrvConn)).Databases
    $srv | Add-Member -type NoteProperty "Servername" $server
    $dbs += $srv
    }
    $dbs | select servername,name,owner,size | Sort-object size 
    clear-variable -Name dbs

    Assigning the get-content to a variable allows you to use the foreach ($server in $servers) so the server name is available at the end of the pipeline.  

    Assigning the .databases element to $srv instead of the whole object allows you to add the server property.

    Hope this helps!  I don't have a SQL instance to test...


    • Edited by Rhys W Edwards Thursday, March 20, 2014 9:04 AM Changed "Server" to "Servername" as specified by O.P.
    Thursday, March 20, 2014 9:02 AM