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.

$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