Discussion: how to view and obtain the SQL Server instance name

I. Available when viewing the Instance name

1. Service-SQL Server (Instance name), default instance: (MSSQLSERVER)

Or you can view the local instance when you connect to enterprise management.

2. Through the notebooks table
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft SQL Server/InstalledInstance

3. Use commands
Sqlcmd/osql
Sqlcmd-L
Sqlcmd-Lc
Osql-L

Obtain the availability example. The following is an example.

Copy codeThe Code is as follows: DECLARE @ Table TABLE (instanceName sysname NULL)

Insert @ Table EXEC sys. xp_mongoshell 'sqlcmd-Lc'

-- LEFT (@ serverName, CHARINDEX ('/', @ serverName + '/')-1) Replace the current host name, root verification instance naming rules

SELECT * FROM @ Table WHERE instanceName like left (@ serverName, CHARINDEX ('/', @ serverName + '/')-1) + '%'

II,

-- 1.
Select serverproperty ('instancename ')

-- 2
Sp_helpserver

-- 3
Select @ SERVERNAME

-- 4
SELECT * from sys. SYSSERVERS

-- 5
SELECT * from sys. SERVERS

III,

EXECUTE xp_regread @ rootkey = 'HKEY _ LOCAL_MACHINE ',
@ Key = 'Software/Microsoft SQL Server/Instance Names/SQL ',
@ Value_name = 'mssqlserver'

IV,

Select Case
When SERVERPROPERTY ('instancename') Is Null Then @ SERVERNAME
Else SERVERPROPERTY ('instancename ')
End

5. Obtain the names of all instances locally or on the network.

1. You can do with registry reading, like my code

Copy codeThe Code is as follows: using System;
Using Microsoft. Win32;

Namespace SMOTest
{
Class Program
{
Static void Main ()
{
RegistryKey rk = Registry. LocalMachine. OpenSubKey (@ "SOFTWARE/Microsoft SQL Server ");
String [] instances = (String []) rk. GetValue ("InstalledInstances ");
If (instances. Length> 0)
{
Foreach (String element in instances)
{
If (element = "MSSQLSERVER ")
Console. WriteLine (System. Environment. MachineName );
Else
Console. WriteLine (System. Environment. MachineName + @ "/" + element );
}
}
}
}
}

2. You can use SQLDMO. dll to retrieve the list of SQL Server instances. the SQLDMO. dll can be found from the "C:/Program Files/Microsoft SQL Server/80/Tools/Bin" folder. refer this assembly in your project and the following snippet wocould return a List Object containing the SQL server instances.Copy codeThe Code is as follows: public static List GetSQLServerInstances ()
{
NameList sqlNameList = null;
Application app = null;

Var sqlServers = new List ();
Try
{
App = new ApplicationClass ();
SqlNameList = app. ListAvailableSQLServers ();
Foreach (string sqlServer in sqlNameList)
SqlServers. Add (sqlServer );
}
Catch (Exception ex)
{
// Play with the exception.
}
Finally
{
If (sqlNameList! = Null)
SqlNameList = null;
If (app! = Null)
App = null;
}
Return sqlServers;
}