How to determine LocalDb instance name in SQL script?

Question

For a non-LocalDb SQL Server, I can SELECT SERVERPROPERTY('ServerName') to get the name of the server and the instance that I'm running on. For a LocalDb server, however, I get SERVERNAME\LOCALDB#SOMEHASH. How can I get the local instance name?

SOLUTION:

The solution for getting the instance, working on both LocalDB and "normal" SQL Server instances:

DECLARE @serverName NVARCHAR(151)
IF SERVERPROPERTY('IsLocalDb') = 1
    SELECT @serverName = 'np:\\.\pipe\' + CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceName')) + '\tsql\query'
ELSE
    SELECT @serverName = CONVERT(NVARCHAR(128), SERVERPROPERTY('ServerName'))

Some background that I should have mentioned in the original question: We have a configuration database for our application. Among other things, it stores connection strings. For developer machines and integration tests, we want to be able to generate a database with a script, and have the connection string refer to the local instance. Thus, I need to have a working connection string (which LOCALDB#SOMEHASH is not). Since there is some spread in edition of database server, I needed something that could handle both cases.

Answer 1

Is this:

SELECT SERVERPROPERTY ('InstanceName')

EDIT

from link

Connecting to a Shared Instance of LocalDB

To connect to a shared instance of LocalDB add .\ (dot + backslash) to the connection string to reference the namespace reserved for shared instances. For example, to connect to a shared instance of LocalDB named AppData use a connection string such as (localdb).\AppData as part of the connection string. A user connecting to a shared instance of LocalDB that they do not own must have a Windows Authentication or SQL Server Authentication login.

and

If your application uses a version of .NET before 4.0.2 you must connect directly to the named pipe of the LocalDB. The Instance pipe name value is the named pipe that the instance of LocalDB is listening on. The portion of the Instance pipe name after LOCALDB# will change each time the instance of LocalDB is started. To connect to the instance of LocalDB by using SQL Server Management Studio, type the Instance pipe name in the Server name box of the Connect to Database Engine dialog box. From your custom program you can establish connection to the instance of LocalDB using a connection string similar to SqlConnection conn = new SqlConnection(@"Server=np:\\.\pipe\LOCALDB#F365A78E\tsql\query");