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:yourprojectsrc"# db objects (e.g. DDL for views, sprocs, triggers, functions)$sqlsrc = "C:yourprojectsqlscripts"# connection string$db = "Data Source=localhost;Initial Cataog..."# ---------------------------------------------------------echo "Looking for stored procedures..."$cn = new-object system.data.SqlClient.SqlConnection($db)$q = "SELECT nameFROM sys.objectsWHERE 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.Countecho " found $count stored procedures"# search in C# filesecho "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 filesecho "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 objectsdir -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.Countecho "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.
