How many SQL Server Instances are there ?

In most of the scenario, We would need to identify that how many instances are there or How many instances installed in current server.

1. Login into SQL Server in which server you want to...
2. Copy & Paste the script given below and hit your 'F5' (or) Execute.

CREATE TABLE #Instances
(
InstanceName VARCHAR(100)
)

INSERT #Instances
EXEC('xp_cmdshell ''SQLCMD -L''')

DECLARE @ServerName VARCHAR(100)
SELECT @ServerName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR)

SELECT LTRIM(InstanceName) [Instance Name] FROM #Instances WHERE LTRIM(InstanceName) LIKE @ServerName + '%'

DROP TABLE #Instances


3. Wow... Now you got the list of Instances installed on the current server.

1 comment:

  1. hi buddy

    It's very useful information, I was trying to read it from the registry, But, I faild to read multiple instances.

    But, This is very simple and recommended thing.

    Thank you buddy, I'll follow you

    ReplyDelete