$server = "RHYS-PC\SQL2005";
$database = "AdventureWorks";
$matchText = "Person";
 
# Load the SQL Management Objects assembly (Pipe out-null suppresses 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 procedure
	if($proc.TextBody -match $matchText)
	{
		Write-Host "Procedure: " $proc.Name " contains $matchText";
	}
}
 
# For each function in the database
foreach($func in $db.UserDefinedFunctions)
{
	# For each matching user defined function
	if($func.TextBody -match $matchText)
	{
		Write-Host "Function: " $func.Name " contains $matchText";
	}	
}
 
# For each view in the database
foreach($view in $db.Views)
{
	# For each matching view
	if($view.TextBody -match $matchText)
	{
		Write-Host "View: " $view.Name " contains $matchText";
	}
}
 
# For each trigger in the database
foreach($trigger in $db.Triggers)
{
	# For each matching trigger
	if($trigger.TextBody -match $matchText)
	{
		Write-Host "Trigger: " $trigger.Name " contains $matchText";
	}
}