SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server Tools Reviews and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



List of SQL Servers using sqlcmd Utility

sqlcmd Utility enables database administrators to list servers where SQL Server is installed in local network. List of SQL Servers in your network can be prepared by using running sqlcmd utility sqlcmd.exe with -Lc option easily. If you carefully check the SQL Server sqlcmd utility options, you will see the -L option and its "c" optional parameter. The -L sqlcmd command option provides the easiest way for SQL Server database administrators to lists names of the locally configured server computers broadcasting on the network.

It is a common requirement for many SQL Server database administrators to find out any unknown SQL Server database server installations in their responsibility areas. For reports and to list installed SQL Server computers, the easiest method is to use SQLCMD Utility provided by Microsoft which comes default with SQL Server installation.

If SQLCMD utility is used with -L option, it generates a list of locally configured server computers. In the SQL Server list the names of the servers broadcasting on the network are listed. SQLCMD can list servers up to 3000 SQL Server names in the execution result.

If "c" (clean output) optional paramater is used with -L (list servers) option, only the SQL Server names will be returned in the result dataset of SQLCMD utility execution. Unnecessary text and informative descriptions will be omitted therefore the result set is called as clean output of list of SQL Servers in the network.

For more syntax options and sqlcmd parameters that SQL developers or database administrators to use with sqlcmd commands, please read the SQL reference for sqlcmd at MSDN.

SQL Server List

A SQL Server database administrator or a database developer can open the CMD promt and following sqlcmd command in the command promt to list SQL Server computers in the network.

SQLCMD Utility to list SQL Server machines

SQLCMD -Lc

And the returned list of SQL Server installed servers will be as similar in the following screenshot. Since c clear output option is stated beside -L list option in sqlCmd utility, only the SQL Server names are listed without any additional information.

list of SQL Server installed servers in the network

Of course, it is also possible to run SQLCmd utility command from SQL Server Management Studio query editor window. The only restriction may be the use of xp_cmdshell. If xp_cmdshell use is enabled on the SQL Server instance, admins or developers can execute following SQL command at query editor window.

EXEC xp_cmdshell 'sqlcmd -Lc'

The execution of the above xp_cmdshell SQL statement will return the same SQL Server list running locally in the network.

Using SQLCmd and xp_cmdshell utility tools in SQL Server, I code following SQL stored procedure which will store installed SQL Server computer names in the network and list SQL Server database servers as a result set. If you are using SQL Server 2005, you can use following SQL procedure too.

CREATE PROC sp_ListSQLServersInNetwork
AS

IF OBJECT_ID('SQLServerListInNetwork', 'table') is null

 CREATE TABLE SQLServerListInNetwork (
  id int identity(1,1),
  servername nvarchar(100)
 )

TRUNCATE TABLE SQLServerListInNetwork
INSERT INTO SQLServerListInNetwork (servername) EXEC xp_cmdshell 'sqlcmd -Lc'

DELETE FROM SQLServerListInNetwork WHERE servername IS NULL
SELECT * FROM SQLServerListInNetwork

GO

EXEC sp_ListSQLServersInNetwork

But if you have a SQL Server 2008 or a successor version of SQL Server 2008 where SQL Merge command can be used, I can suggest the below stored procedure. You can use this stored procedure to list SQL Servers on your network and run periodically by using a SQL job to trace new installations and uninstallations.

CREATE PROC sp_ListSQLServersInNetwork
AS

IF OBJECT_ID('SQLServerListInNetwork', 'table') is null

 CREATE TABLE SQLServerListInNetwork (
  servername nvarchar(100),
  firstactivity datetime,
  lastactivity datetime
 )


DECLARE @serverlist AS TABLE (servername sysname null, activitydate datetime default getdate())

INSERT INTO @serverlist(servername) EXEC xp_cmdshell 'sqlcmd -Lc'
DELETE FROM @serverlist WHERE servername IS NULL

MERGE SQLServerListInNetwork
USING
(
 SELECT servername, activitydate firstactivity, activitydate lastactivity
 FROM @serverlist
) NewData ON SQLServerListInNetwork.servername = NewData.servername
WHEN MATCHED THEN
 UPDATE SET SQLServerListInNetwork.lastactivity = NewData.lastactivity,
  SQLServerListInNetwork.firstactivity = ISNULL(SQLServerListInNetwork.firstactivity, NewData.lastactivity)
WHEN NOT MATCHED BY TARGET THEN
 INSERT VALUES(servername, firstactivity, lastactivity);

SELECT * FROM SQLServerListInNetwork
GO

EXEC sp_ListSQLServersInNetwork

As a result, all above methods for obtaining a SQL Server list on your network use the SQLCMD utility sqlcmd.exe -Lc option. But for a database administrator to have a report of SQL Servers list is very valuable. I hope we can find a way to extend the above stored procedures to list all SQL Server instances on the current network.






Related SQL Resources

SQL Server Articles

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server Tools

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems