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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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


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

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

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

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

DELETE FROM SQLServerListInNetwork WHERE servername IS NULL
SELECT * FROM SQLServerListInNetwork


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

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
 SELECT servername, activitydate firstactivity, activitydate lastactivity
 FROM @serverlist
) NewData ON SQLServerListInNetwork.servername = NewData.servername
 UPDATE SET SQLServerListInNetwork.lastactivity = NewData.lastactivity,
  SQLServerListInNetwork.firstactivity = ISNULL(SQLServerListInNetwork.firstactivity, NewData.lastactivity)
 INSERT VALUES(servername, firstactivity, lastactivity);

SELECT * FROM SQLServerListInNetwork

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.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.