CREATE TABLE [Person].[Address2](
	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[AddressLine1] [nvarchar](60) COLLATE Latin1_General_CS_AS NOT NULL,
	[AddressLine2] [nvarchar](60) COLLATE Latin1_General_CS_AS NULL,
	[City] [nvarchar](30) COLLATE Latin1_General_CS_AS NOT NULL,
	[StateProvinceID] [int] NOT NULL,
	[PostalCode] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Address_rowguid2]  DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Address_ModifiedDate2]  DEFAULT (getdate()),
 CONSTRAINT [PK_Address_AddressID2] PRIMARY KEY CLUSTERED 
(
	[AddressID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

$server = "localhost\sql2005";					# The SQL Server instance name
$database = "AdventureWorks";					# The database name
$matchText = "\[Person\].\[Address\]";			# Definition text to search .Be aware this accepts a regular expression
$replaceText = "[Person].[Address2]";			# Text to replace $matchText
$alter = $false;								    # Set to true if you want the script to alter database objects
$backupFolder = "E:\powershell\backup\";	    # Change script folders. Need a \ (back slash) on the end
$changeFolder = "E:\powershell\change\"		    # One file per object, backup & change folders
 
# Load the SQL Management Objects assembly (Pipe out-null supresses output)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
 
# Create our SMO objects
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server;
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database");
 
# Get the database
$db = $srv.Databases[$database];
 
# For each stored procedure in the database
foreach($proc in $db.StoredProcedures)
{
	# For each matching stored prcoedure
	if($proc.TextBody -match $matchText)
	{
		Write-Host "Processing proc: " $proc.Name;
		# Backup of the original proc definition
		$proc.Script() | Out-File ($backupFolder + "procs\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$proc.name + "_backup.sql");
		# New procedure definition sql
		$proc.Script() -replace($matchtext, $replaceText) | Out-File ($changeFolder + "procs\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$proc.name + ".sql");
		# If set to true this will change the procedure definition on the server!
		if($alter)
		{
			$proc.TextBody = $proc.TextBody -replace($matchtext, $replaceText);
			$proc.Alter();
			Write-Host "Altered " $proc.Name;
		}
	}
}
 
# For each view in the database
foreach($view in $db.Views)
{
	# For each matching view
	if($view.TextBody -match $matchText)
	{
		Write-Host "Processing view: " $view.Name;
		# backup the original view definition
		$view.Script() | Out-File ($backupFolder + "views\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$view.name + "_backup.sql");
		# New procedure definition sql
		$view.Script() -replace($matchtext, $replaceText) | Out-File ($changeFolder + "views\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$view.name + ".sql");
		# If set to true this will change the view definition on the server!
		if($alter)
		{
			$view.TextBody = $view.TextBody -replace($matchtext, $replaceText);
			$view.Alter();
			Write-Host "Altered " $view.Name;
		}
	}
}
 
# For each trigger in the database
foreach($trigger in $db.Triggers)
{
	# for each matching trigger
	if($trigger.TextBody -match $matchText)
	{
		Write-Host "Processing trigger: " $trigger.Name;
		# backup the original trigger definition
		$trigger.Script() | Out-File ($backupFolder + "triggers\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$trigger.name + "_backup.sql");
		# New trigger definition sql
		$trigger.Script() -replace($matchtext, $replaceText) | Out-File ($changeFolder + "triggers\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$trigger.name + ".sql");
		# If set to true this will change the trigger definition on the server!
		if($alter)
		{
			$trigger.TextBody = $trigger.TextBody -replace($matchtext, $replaceText);
			$trigger.Alter();
			Write-Host "Altered " $trigger.Name;
		}
	}
}
 
# For each UDF in the database
foreach($udf in $db.UserDefinedFunctions)
{
	# for each matching udf
	if($udf.TextBody -match $matchText)
	{
		Write-Host "Processing UDF: " $udf.Name;
		# backup the original udf definition
		$udf.Script() | Out-File ($backupFolder + "functions\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$udf.name + "_backup.sql");
		# New udf definition sql
		$udf.Script() -replace($matchtext, $replaceText) | Out-File ($changeFolder + "functions\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$udf.name + ".sql");
		# If set to true this will change the udf definition on the server!
		if($alter)
		{
			$udf.TextBody = $udf.TextBody -replace($matchtext, $replaceText);
			$udf.Alter();
			Write-Host "Altered " $udf.Name;
		}	
	}
}
 
Write-Host "Finished processing  $database on $server.";

$matchText = "\[Person\].\[Address2\]";			# Definition text to search .Be aware this accepts a regular expression
$replaceText = "[Person].[Address]";			# Text to replace $matchText