Skip to content

Instantly share code, notes, and snippets.

@GeneralTesler
Last active December 15, 2019 03:44
Show Gist options
  • Save GeneralTesler/c36011a88c8fff1bad8a24f476ca686e to your computer and use it in GitHub Desktop.
Save GeneralTesler/c36011a88c8fff1bad8a24f476ca686e to your computer and use it in GitHub Desktop.
Search MSSQL databases for columns containing keywords using SqlServer PowerShell PSDrive
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment