Vista - Find & Replace in MSSQL Tables through PowerShell

Greetings,

I am (very) new to PowerShell and I'm trying to absorb all it has to offer. Some of my colleagues have written a script to search within a MSSQL database, find a line of text in a stored procedure, and replace said text with new text. I am trying to enhance this script by trying to perform the same type of find and replace functionality, but within all the tables in a MSSQL database. I've tried to modify the code below that does the SP's to account for tables instead, but I am at a loss. Because I am new to PS I am sure my syntax is wrong, and since a table consists of rows and columns / fields I'm sure that it may be more complex. By any chance does anyone know how I can modify the code below to find and replace text in database tables?


[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")


$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "SERVERNAME"

foreach ($database in $server.Databases) {
if ($database.Name -match "DATABASENAME") {
foreach ($sp in $database.StoredProcedures) {
if ($sp.TextBody -match "TEXT1") {
$sp.Script() | Out-File ([string]$server.name + "_" + [string]$database.Name + " " + [string]$sp.name + ".sql")
$sp.TextBody = $sp.TextBody -replace("TEXT1", "TEXT2")
$sp.Alter()
}
}
}
}


Thanks in advance!

OK, I follow now. Can't try it right now, but there may be a way...
Might not be pretty...

Marco