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”.
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)
87 Comments. Leave new
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…
while running cmd you should run sql sever in the background
I think you are correct. Thanks Avinash.
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.
Hello Pinal,
How do you get all systems that connect to a server
In SQL Server, you can use select * from sysprocesses and get details about various processes running.
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.”
both osql -L and sqlcmd -L is not working.what to do now.
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>
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.
Thanks for sharing your situation Ashi
Hi Mr Pinal .. can you please try (DOS Command: net Start it is list all services but very fast)
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
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()
-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
dont login as admin. login as a domain user
SQL Servers. If SQL Server Browser is off, then you can’t use sqlcmd -L.