Powershell script to find orphan stored procedures

Spread the love

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.