SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network

I recently had requirement to create list of all the SQL Server on local network. I remembered that I had written similar script a year ago SQL SERVER – Script to Find SQL Server on Network. When I looked at it, I realize that I had written it for SQL Server 2000 and used “isql” utility, which is deprecated now. I quickly wrote down updated script using “sqlcmd”. Command “osql” still works in SQL Server 2008.

Go to command prompt and type in “osql -L” or “sqlcmd -L”.

SQL SERVER - Find All Servers From Local Network - Using sqlcmd - Detect Installed SQL Server on Network sqlcmdl

Note one change between osql and sqlcmd is that osql has additional server “(local)” listed in the servers list which is in fact same as “SQL” in my case. While “sqlcmd” gives accurate result.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Practical SQL Server XML: Part One – Query Plan Cache and Cost of Operations in the Cache
Next Post
SQLAuthority News – 900th Article – 9 Best Practices – Important Milestones

Related Posts

86 Comments. Leave new

  • I got no active server by “sqlcmd -L” now what should i have to do..?
    please help..

    Reply
  • TOTALLY frustrated with MS SQL … every were I look they say OSQL -L or sqlcmd -L will give you a list of servers/instances, BUT, I too receive NONE or no list at all… I see lots of folks having this same problem, bot I see no workaround or fixes…

    Reply
  • Suraj Pardeshi
    July 3, 2014 6:23 pm

    How do I ensure that My server is not available in a Virtual Server Environment in Network servers’ list? Coz I dont want anybody from outside my org to even connect and try some pwd using default SA login….
    Any help would certainly help us lot here.

    Reply
  • Hello Pinal,

    How do you get all systems that connect to a server

    Reply
    • In SQL Server, you can use select * from sysprocesses and get details about various processes running.

      Reply
  • how to get SQL instance name with the help of command … I tried sqlcmd commands where its not working ..its throwing ” not recognized as an internal or external command,
    operable program or batch file.”

    Reply
  • both osql -L and sqlcmd -L is not working.what to do now.

    Reply
  • hi Pinnal,
    I have same problem. i cant see my server in cmd.
    there is result —NONE–
    C:\Users\Akram Bagwan>osql -L

    Servers:
    — NONE —

    C:\Users\Akram Bagwan>sqlcmd -L

    Servers:
    ;UID:Login ID=?;PWD:Password=?;Trusted_Connection:Use Integrated Security=?;
    *APP:AppName=?;*WSID:WorkStation ID=?;

    C:\Users\Akram Bagwan>

    Reply
  • Very helpful..I was installing the SQL Server 2012 on Windows Server 2012 as it’s a pre-requisite for installing SCCM 2012 R2.. Spent about an hour and a half to establish a connection. Couldn’t successfully establish the connection until UDP 1433 port was also opened on the Firewall.

    Reply
  • Hi Mr Pinal .. can you please try (DOS Command: net Start it is list all services but very fast)

    Reply
  • Hi Mr Pinal actually I want to check connected system status with sql server database and want to kill that indivisual system session

    for this in sql server any database command or any oprocedure

    Please help me I will be very apprecieted

    Reply
  • Use this command to return the running instance sof sql servers on your company network. Please note this a powershell command

    [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

    Reply
  • -Run PowerShell > Exit to root directory using cd.. dos command. > provide the following command in powershell mode.

    PS C:>[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

    ServerName InstanceName IsClustered Version
    ————- —————— —————- ————–
    WIN2K3SQL INST02 No 9.00.4035.00
    WIN2K3SQL SQL2008 No 10.50.1352.06

    Reply
  • dont login as admin. login as a domain user

    Reply

Leave a Reply

Menu