Searching database objects with Powershell

Sometimes it’s useful to get a quick overview of what objects are referencing a particular table, view or function. This may arise when we think we may need to drop an object but want to double-check if anything in the database is still referencing it. Here’s a quick solution in the form of a Powershell script. To get started you just need to modify the values for a few variables before executing the script.

  • $server – The SQL Server instance you wish to search against.
  • $database – The database you wish to search.
  • $matchText – The text you wish to search for in the objects.

This script will search the all of the stored procedures, functions, views and triggers for the text specified in $matchText.

?View Code POWERSHELL
$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";
	}
}

Here’ an example of the output when run against the AdventureWorks database searching objects for "Person".

searching database objects for text


2 Comments

  1. […] Searching database objects with Powershell – “Sometimes it’s useful to get a quick overview of what objects are referencing a particular table, view or function.” […]

  2. melethil says:

    Thanks a lot, I am still learning PS, this helped a lot.

Leave a Reply