CODEWITHSUNDEEP
sql-serverpowershell
krmarshall87
krmarshall87

Reputation: 191

How to get SQL Server database name without logging in?

I have hundreds of servers and would like to check for the existence of non-System SQL Server databases running on them. They could be a variety of versions: 2005, 2008, 2012, Express.

I wasn't sure if the database names were stored in a particular registry (though I couldn't find it) or file/filename (on C: drive) and could pull the info that way. I would like to be able to do this from a Powershell script if possible, but I could throw it into batch.

Upvotes: 1

Views: 3349

Answers (2)

Cookie Monster
Cookie Monster

Reputation: 1791

Assuming you have privileges to do so, T-SQL and using the SQL Server Management Objects (SMO) might be your best bet. References abound on running T-SQL or using the SMO from PowerShell, including this one.

Example Using T-SQL

# Download and dot source Invoke-Sqlcmd2
# source - https://raw.githubusercontent.com/RamblingCookieMonster/PowerShell/master/Invoke-Sqlcmd2.ps1
. "\\Path\To\Invoke-Sqlcmd2.ps1"

#Run sp_databases, among other options, against your SQL servers.
"Server1", "Server2\NamedInstance" |
    Invoke-Sqlcmd2 -query "Sp_databases" -AppendServerInstance

Example using SQL SMO

#Download, install components from SQL feature pack:
    # SQLSysClrTypes, SharedManagementObjects, optionally PowerShellTools
    # http://www.microsoft.com/en-us/download/details.aspx?id=42295

#One of several ways to load one of the SMO binaries
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

#Connect to the server with integrated Windows auth
$Connection = New-Object "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList Server1           

#List the databases
$Connection.Databases

#List the names of the databases
$Connection.Databases | Select -ExpandProperty Name

Lastly, it sounds like you are concerned with discovery. Before you look for individual databases, you might consider looking for SQL instances, which you could then investigate on a case by case basis. Boe Prox wrote Get-SQLInstance which only requires registry privileges and will spit back SQL instance names, SQL version, and other helpful info.

Upvotes: 2

user3777201
user3777201

Reputation:

The databases on a SQL Server instance are not stored in the registry or in flat files anywhere on the system. It's stored in the master database, in system tables.

The only place where you may be able to get data, depending on your permissions to the server, is through WMI. See this answer on StackOverflow. The values in WMI are not stored on the system, but SQL Server speaks to it through the provider interface, the data is still coming from the SQL Server Instance.

Those are really your only two choices, log into the server and run a TSQL Query or use WMI.

Upvotes: 1

Related Questions