There are multiple ways by which we can find a list of SQL Server instances name instances on a machine. In this blog, I would show a few ways to do it.
Command Prompt
Based on my research, registry key would have an entry whenever SQL is installed. So we can query the registry key to find details about instance name.
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL
If we want to query services, then we can search for all services and look for display name
search query | findstr "DISPLAY_NAME"|findstr /C:"SQL Server ("The above method of registry, can’t tell the version.
PowerShell
Whatever we did on method 1, same can be achieved using PowerShell also. Below is the method where we query the registry.
Get-ItemProperty ‘HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL’
If we want to query services to get then another option would be to use below PowerShell
Get-Service | ?{ $_.DisplayName -like "SQL Server (*" }Do you know any more ways? Please share with others via comment.
Here is my earlier article on the same subject:
- SQL SERVER – Find Name of The SQL Server Instance
- SQL SERVER – Introduction to SERVERPROPERTY and example
- SQL SERVER – Find Hostname and Current Logged In User Name
- SQL SERVER – Q&A: SQL Clustering Virtual Server Name and Instance Name
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
I think your meant to say ‘sc query’ vs ‘search query’
Here is what I use:
# import SQLPS if necessary
if((get-module|Where-Object {$_.Name -like “sqlps”}|Measure-Object).Count -eq 0){Import-Module sqlps};
Get-ChildItem SQLServer:\SQL\$server_name;
Pulling SQL Server instances from the registry is a little cleaner if you use:
(Get-ItemProperty ‘HKLM:\Software\Microsoft\Microsoft SQL Server\’).InstalledInstances
Getting instance names from Get-Service and just return the instance name:
(Get-Service -DisplayName “SQL Server (*”).DisplayName -replace ‘.*\((.*)\).*’, ‘$1’
Nice.
It seems it doesn’t return (LocalDB) instances
– T-SQL Query to find list of Instances Installed on a machine
DECLARE @GetInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))
Insert into @GetInstances
EXECUTE xp_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key = ‘SOFTWARE\Microsoft\Microsoft SQL Server’,
@value_name = ‘InstalledInstances’
Select InstanceNames from @GetInstances
These are great, but I have been trying to find a way to list all databases in all of the instances on one SQL server. We have a number of servers with over 12 SQL instances and many databases that reside on those instances. Is there any way to do this? All I find are ways to get the databases from a single instance.
Hi Cheryl,
The Microsoft Assessment and Planning (MAP) Toolkit is very good for this, assuming you have enumeration rights on the databases
I have the same exact predicament. Did you ever figure it out? Ideally we should get a list with a column for the InstanceName and a column for the database name in the query results. Kindly let me know if you figured it out. THANK you in advance, Raphael.
Thank you, Zol’taan! That is PERFECT if I do not want to mess with PowerShell and such. Question: Would you known if there is a way to also include the databases inside each instance? So, we would have in the query results one column with the instance name, and another column with the databases belonging to the instance. I hope I explained it clearly enough. TIA, Raphael