Working on a legacy application that uses over 2,200 stored procedures, it can be hard to keep track of which ones are still active and which can be deleted.

Here’s a quick PowerShell script I wrote that locates stored procedures in a database that aren’t referenced by code or other database objects (assuming you have them scripted in source control).

# find un-used stored procedures
# ---------------------------------------------------------

# C# files
$src = "C:\yourproject\src"

# db objects (e.g. DDL for views, sprocs, triggers, functions)
$sqlsrc = "C:\yourproject\sqlscripts"

# connection string
$db = "Data Source=localhost;Initial Cataog..."

# ---------------------------------------------------------

echo "Looking for stored procedures..."
$cn = new-object system.data.SqlClient.SqlConnection($db)

$q = "SELECT
	name
FROM
	sys.objects
WHERE
	type in ('P', 'PC')
	AND is_ms_shipped = 0
	AND name NOT IN
	(
		'sp_alterdiagram', -- sql server stuff
		'sp_creatediagram',
		'sp_dropdiagram',
		'sp_helpdiagramdefinition',
		'sp_helpdiagrams',
		'sp_renamediagram',
		'sp_upgraddiagrams'
	)
ORDER BY
	name ASC"

$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$ds = new-object "System.Data.DataSet" "dsStoredProcs"
$da.Fill($ds) | out-null

# chuck stored procs name in an array
$sprocs = New-Object System.Collections.Specialized.StringCollection
$ds.Tables[0] | FOREACH-OBJECT {
	$sprocs.Add($_.name) | out-null
}
$count = $sprocs.Count
echo "  found $count stored procedures"

# search in C# files
echo "Searching source code..."
dir -recurse -filter *.cs $src | foreach ($_) {
	$file = $_.fullname

	echo "searching $file"
	for ($i = 0; $i -lt $sprocs.Count; $i++) {
        $sproc = $sprocs[$i];
		if (select-string -path $file -pattern $sproc) {
			$sprocs.Remove($sproc)
			echo "  found $sproc"
		}
	}
}

# search in NHibernate *.hbm.xml mapping files
echo "Searching hibernate mappings..."
dir -recurse -filter *hbm.xml $src | foreach ($_) {
	$file = $_.fullname

	echo "searching $file"
	for ($i = 0; $i -lt $sprocs.Count; $i++) {
        $sproc = $sprocs[$i];
		if (select-string -path $file -pattern $sproc) {
			$sprocs.Remove($sproc)
			echo "  found $sproc"
		}
	}
}

# search through other database objects
dir -recurse -filter *.sql $sqlsrc | foreach ($_) {
	$file = $_.fullname

	echo "searching $file"
	for ($i = 0; $i -lt $sprocs.Count; $i++) {
		$sproc = $sprocs[$i];
		if ($file -notmatch $sproc) {
                	if (select-string -path $file -pattern $sproc) {
				$sprocs.Remove($sproc)
				echo "  found $sproc"
			}
		}
	}
}

# list any that are still here (i.e. weren't found)
$count = $sprocs.Count
echo "Found $count un-used stored procedures."
for ($i=0; $i -lt $count; $i++) {
	$x = $sprocs[$i]
	echo "  $i. $x"
}

It ain’t too pretty, but it does the job.

I love Windows PowerShell. It’s the missing shell/scripting environment that could never be properly satisfied with just DOS commands and batch files. I know it’s been out for a while now, but I’ve only recently started getting on top of it, and it’s been fantastic so far.

Quite often I find myself needing to do little maintenance jobs involving iterating through files and tranforming them — C# source code, SQL scripts, my MP3 collection etc. Powershell is the perfect tool for these sorts of tasks. Here’s a very simple (and probably not that efficient) snippet I wrote last week to strip out SQL Server Management Studio’s useless ‘descriptive headers’ comments from a nested hierarchy of 2,000 generated SQL scripts:

/****** Object:  Table [dbo].[hibernate_unique_key]    Script Date: 04/12/2009 11:35:29 ******/
CREATE TABLE [dbo].[hibernate_unique_key](
	[next_hi] [int] NULL
) ON [PRIMARY]

GO

dir -recurse -filter *.sql $src | foreach ($_) {
	$file = $_.fullname
	echo $file
	(get-content $file) | where {$_ -notmatch "^s?/****** Object:.*$" } | out-file $file
}

How easy was that? I definitely need to learn more of this stuff!