SQL Instance/Database Enumeration Script Version 1.0

IT IS ALIVE...

Through weeks of hard work I have created the following scripts that will get a list of SQL servers\instances, and will list out the databases for each server\instance.  Please forgive the code formatting.  The cut and paste to the blog page is wonky.

Get-instancesCMDLET.ps1
import-module activedirectory

$names = get-adcomputer -Filter * -Properties * -SearchBase '<LDAP base>' -ResultPageSize 1000 | where-object {$_.Name -NOTLIKE '*clus*'}

foreach($Name in $names) {

$result = Test-Connection -ComputerName $Name.name -Quiet # checks if the server is online

if ($result) {
 $objReg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Name.name)

$objRegKey= $objReg.OpenSubKey("SOFTWARE\\MICROSOFT\\MICROSOFT SQL SERVER" )

if ($objRegKey -ne $null) {

$instances = $objRegKey.GetValue("InstalledInstances", $null)


if ($instances.Count -gt 0) {

foreach($i in $instances) {

if ($i -match 'MSSQLSERVER') {

$output = $Name.name

} Else {

$output = $Name.name + '\' + $i




  } $output | out-file -Append server_instance.csv

} #end of $instances foreach




  } # end of if $instances.count

} # end of if $objRegKey

} # end of if $results

} # end of $names foreach 


The -Searchbase should be the OU in which you search for the servers, i.e., OU=Server,DC=abc,DC=com
Make sure the server_instances.csv file is deleted before you run the script so you don't have a file of redundant data.

Get-SQLServerInstanceDatabase.ps1 
function Get-DBList ($server)



{
  $srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server # creates SMO object for server

$srv.Databases | Select name # returns database name(s)

 } #end Get-DBList function




  
$dbservers = import-csv server_instance.csv # this loads the SQL servers and instances created in the Get-InstancesCMDLET.ps1 script

$dbexclude = 'tempdb','model','msdb','master','dbmaint' # these are tables that every SQL server creates

foreach ($dbserver in $dbservers) {

$dbs = Get-DBList $dbserver.DBInstance # puts server name and instance in the variable

foreach ($db in $dbs) {

if ($db.name -notin $dbexclude) {

$hold = $dbserver.dbinstance + ',' + $db.Name

$hold | out-file -Append fullinstanceDB.txt

} # end if

} # end of $dbs foreach

} # end of $dbservers foreach



　

Again, make sure the server_instances.csv file is deleted before you run the script so you don't have a file of redundant data.