Skip to content

Instantly share code, notes, and snippets.

@GeneralTesler
Last active Dec 15, 2019
Embed
What would you like to do?
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