Avatar of systemgruppen
systemgruppen
 asked on

Powershell get db from list

Hi

I would like to have a powershell script, the takes the contest(SQL server names) of an csv file, and then list all the databases in an output csv file, but I cant get it to work.

The script I use is this one:
"Import-Module sqlps –DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
#Import CSV
$servers = Import-Csv -Path "C:\servers.csv"
foreach ($sv in $servers)
{
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $servers
$SMOserver.Databases | select $servers,Name | Format-Table > c:\sql_db.txt
}"  

Thanks in advanced.
Microsoft Server OSMicrosoft SQL Server

Avatar of undefined
Last Comment
systemgruppen

8/22/2022 - Mon
becraig

Do you get any output at all or are you facing a particular error ?

First I would have to look at smo though there are other options.

Second you keep overwriting the file so only the last DB info will be present.

 | Format-Table > c:\sql_db.txt
needs to be changed to

| Format-Table >> c:\sql_db.txt
systemgruppen

ASKER
Nothing happens - no errors and no c:\sql_db.txt
ASKER CERTIFIED SOLUTION
oBdA

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
becraig

If you do have server and instance name in a csv and need to use smo:
import-csv file.csv |  % {
$sqlsrv = $_.server; $inst = $_.instance
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$sqlsrv\$inst"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name | out-file c:\$sqlsrv .txt -append}
}

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
systemgruppen

ASKER
Hi oDbA

The script works very fine - so thank you.

Its from a csv file, with one server per line, and I've changed the output to a csv file.
Is it possible to insert an empty line between the servers and is it also possible to put the servername in one column and the databasename in the next?

Thanx in advanced
oBdA

The question about the servers.csv was whether it is a real csv, that is, content lines with several columns separated by a delimiter, or a simple text file with (only) one server per line.
If the former: does the file contain a header line, and if not, please specify how many columns there are and which one contains the server name (or post a short sample).
Since the function returns an array of custom objects, a simple Export-Csv should create separate columns.
This now adds an empty line after each name list (and assumes a simple text file for the server names):
Function Get-SqlDatabases {
[CmdletBinding()]
Param(
	[Parameter(ValueFromPipeline=$True, ValueFromPipelineByPropertyName=$True, Position=0)]
	[string]$Server
)
	Process {
		Try {
			$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
			$SqlConnection.ConnectionString = "Server = $($Server); Database = master; Initial Catalog = master; Integrated Security = True"
			$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
			$SqlCmd.CommandText = "select name from sys.databases"
			$SqlCmd.Connection = $SqlConnection
			$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
			$SqlAdapter.SelectCommand = $SqlCmd
			$DataSet = New-Object System.Data.DataSet
			$RowCount = $SqlAdapter.Fill($DataSet)
			$SqlConnection.Close()
			If ($RowCount -gt 0) {
				$DataSet.Tables[0].Rows | Select-Object -Property `
					@{Name="Server"; Expression={$Server}},
					@{Name="Database"; Expression={$_.Name}} | Write-Output
			}
		} Catch {
			If (-Not ($ErrorActionPreference -eq [System.Management.Automation.ActionPreference]::SilentlyContinue)) {
				$_.Exception.ErrorRecord | Write-Error
			}
		}
	}
}

Get-Content C:\Servers.csv | Get-SqlDatabases | % {
	$_; "" | Select-Object -Property @{Name="Server"; Expression={""}},	@{Name="Database"; Expression={""}}
} | Export-Csv "C:\sql_db.csv" -NoTypeInformation

Open in new window

systemgruppen

ASKER
Is is just a plain csv file with no header - you can see the attached example, and below here is the output when I run the script:
Server,"Database"
SERVER1,"master"
,""
SERVER1,"tempdb"
,""
SERVER1,"model"
,""
SERVER1,"msdb"
,""
SERVER1,"ReportServer"
,""
SERVER1,"ReportServerTempDB"
Doc1.docx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
oBdA

Sorry, this should work as advertised now:
Function Get-SqlDatabases {
[CmdletBinding()]
Param(
	[Parameter(ValueFromPipeline=$True, ValueFromPipelineByPropertyName=$True, Position=0)]
	[string]$Server
)
	Process {
		Try {
			$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
			$SqlConnection.ConnectionString = "Server = $($Server); Database = master; Initial Catalog = master; Integrated Security = True"
			$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
			$SqlCmd.CommandText = "select name from sys.databases"
			$SqlCmd.Connection = $SqlConnection
			$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
			$SqlAdapter.SelectCommand = $SqlCmd
			$DataSet = New-Object System.Data.DataSet
			$RowCount = $SqlAdapter.Fill($DataSet)
			$SqlConnection.Close()
			If ($RowCount -gt 0) {
				$DataSet.Tables[0].Rows | Select-Object -Property `
					@{Name="Server"; Expression={$Server}},
					@{Name="Database"; Expression={$_.Name}} | Write-Output
			}
		} Catch {
			If (-Not ($ErrorActionPreference -eq [System.Management.Automation.ActionPreference]::SilentlyContinue)) {
				$_.Exception.ErrorRecord | Write-Error
			}
		}
	}
}

Get-Content C:\Servers.csv | % {
	Get-SqlDatabases $_; "" | Select-Object -Property @{Name="Server"; Expression={""}}, @{Name="Database"; Expression={""}}
} | Export-Csv "C:\sql_db.csv" -NoTypeInformation

Open in new window

systemgruppen

ASKER
The output is like this now:
Server,"Database"
SERVER1,"master"
SERVER1,"tempdb"
SERVER1,"model"
,""
SERVER2,"master"
SERVER2,"tempdb"
SERVER2,"model"
SERVER2,"msdb"
oBdA

Well, that's what you requested, isn't it? (Though there should be quotes around the server names as well, but I'm assuming that these went away when you anonymized the server names.)
Is it possible to insert an empty line between the servers and is it also possible to put the servername in one column and the databasename in the next?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
systemgruppen

ASKER
I did not delete anything besides the servernames - not any of the quotes, so the output is as the one I pasted in.