Obtaining a list of all SQL server instances

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Obtaining a list of all SQL server instances

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-31 : 22:02:44

I have tried the following however this seems to only return the instance for which the database is currently running under. I have two servers running and I can see them in management studio. They are:

butterfingers\sqlexpress >
DB = VC + master, model, reportserver etc
butterfingers\xtralis_cyclops
DB = VC + master, model, reportserver etc

SELECT * FROM sys.servers

PS. At the moment they are all on the same PC.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-31 : 23:54:50

That isn't the purpose of sys.servers. That is for the local server, linked servers, and remote servers.

You can check net start via a cmd command (and then call it through xp_cmdshell) and list out the SQL Server instances. You could also write a .NET CLR object to find them. There are plenty of ways to get the locally installed instances, but sys.servers isn't one of them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-03 : 20:47:56

Thanks tkizer for the pointers this seems to work.

Used first function to remove the 3 leading spaces in the name. Then second stored proc to get the list.

USE [VC]
GO
/****** Object: UserDefinedFunction [dbo].[TRIM] Script Date: 09/04/2008 10:16:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[TRIM](@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END

For some reason I dont seem to be able to post the code, my brower keeps coming up with internet explorer cannot display this web page each time I add it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources