Search MSSQL databases for columns containing keywords using SqlServer PowerShell PSDrive

mssql.ps1

Import-Module SqlServer

function Get-SQLTablesByKeyword {
    <#
        Usage:
            Get-SQLTablesByKeyword -Computer <hostname>
        Example:
            Get-SQLTablesByKeyword -Computer sql01.domain.local
        Example Output:
            Table  Database Column                
            -----  -------- ------                             
            TABLE1 DATABASE SSN                                   
            TABLE2 DATABASE BANKACCT                          
            TABLE2 DB2      SOCIAL_SECURITY_NUMBER
            TABLE1 DB2      PASSWORD              
    #>
    
    Param(
        [Parameter()][String]$Computer
    )

    $cwd = pwd

    $keywords = $("ssn", "social", "acct", "password", "bank")

    $sqlbase = "sqlserver:\sql\$Computer\default\databases"

    cd $sqlbase
    $dbs = Get-ChildItem $sqlbase

    $hits = @()
    foreach($db in $dbs){
        cd $db.pspath
        $tables = gci "tables"
        foreach($table in $tables){
            cd $table.pspath
            $columns = gci "columns"
            foreach($column in $columns){
                foreach($keyword in $keywords){
                    if ($column.name.tolower().contains($keyword.tolower())){
                        $hits += New-Object -TypeName PSObject -Property @{"Database"=$db.name; "Table"=$table.name; "Column"=$column.name}
                    }
                }
            }
        }

    }

    cd $cwd
    $hits

}