NO. of instances

Question

Post reply

NO. of instances

SQLCracker

SSC Veteran

Points: 227
More actions
April 23, 2013 at 5:07 am

#276998

can any one suggest me that how to find out the No. of instances in a server using query

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

saptapavank (4/23/2013)
can any one suggest me that how to find out the No. of instances in a server using query

Interview question?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Geoff A SSChampion Points: 11511 More actions · Answer 2

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

Lowell SSC Guru Points: 323504 More actions · Answer 3

Geoff A code example worked fairly well on my machine.

My dev machine has four instances installed:

2008 standard (default).

2005 Express.

2008R2 Developer.

2012 Developer.

The above query seems to have missed my 2005 SQL Express instance, which i clearly see did not place a key in the SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances folder of the registry.

do you think it's caused by 2005 not putting a key there, or that Express instances don't put a key there?

i tried Netstat -a -b on a command line, and i see multiple sqlserver.exe's running and listing to various ports, but can't map them directly to their instances.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 4

ok by looking at a different registry key and looking for Service names, i think this finds them all on my sample machine:

/*

ResultsId ResultsText

233 MSSQL$SQL2005

234 MSSQL$SQL2008R2

235 MSSQL$SQL2012

237 MSSQLSERVER

*/

declare @Results TABLE (ResultsId int identity(1,1) not null primary key,

ResultsText varchar(200) )

insert into @Results(ResultsText)

EXECUTE master..xp_regenumkeys 'HKEY_LOCAL_MACHINE' , 'SYSTEM\CURRENTCONTROLSET\SERVICES\'

select * from @Results

WHERE ResultsText = 'MSSQLSERVER'

OR ResultsText LIKE 'MSSQL$%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!