asked
Powershell export multiple tables to csv
Good day all SQL professionals, I have a business need to export all data from tables that have a certain prefix. Im fairly new to powershell and am struggling to get this to work. I think the variable passed to the sql query is actually System.data.datarow instead of what that row contains. Plz help: $Tables = invoke-sqlcmd -query "SELECT name FROM sys.tables WHERE name LIKE 'ENUM_%'" -database ****** -serverinstance localhost foreach ($Table in $Tables) { write-host -ForegroundColor Green "Creating File $Table.csv" invoke-sqlcmd -query "SELECT * FROM $Table" -database ****** -serverinstance localhost |export-csv -path F:\ENUM_Tables\$Table.csv }
should probably have mentioned that I have been running this as a .ps1 script file from sqlps
How about a little adjustment like this: $Tables = invoke-sqlcmd -query "SELECT name FROM sys.tables WHERE name LIKE 'Movie%'" -database "MovieManagement" -serverinstance "MyServer" foreach ($Table in $Tables) { $TableName = $Table["name"] write-host -ForegroundColor Green "Creating File $TableName.csv" invoke-sqlcmd -query "SELECT * FROM $TableName" -database "MovieManagement" -serverinstance "MyServer" |export-csv -path c:\scripts\$TableName.csv }
Awesome stuff grant, slight amendment though otherwise the script writes over the same file repeatedly; `|export-csv -path c:\scripts\$Table.csv` should be `|export-csv -path F:\ENUM_Tables\$TableName.csv` but im sure thats what you meant.
Yeah, just noticed that myself. I'll edit so that it's clear.
post seems to have removed the backslashes but you get the idea!
Alternatively, wrap text that you want to identify as "code" in \` characters
Although Grant's answer does the job (+1), there is more than one way to skin a cat: Take a look at [Getting Data out of SQL Server from Powershell][1] to see a few examples. [1]:
http://www.sqlservercentral.com/articles/powershell/65324/
worked for me, I just had to add the brackets on line 6 [$TableName]
2 People are following this question.
