SQL SERVER – Script to Find SQL Server on Network « Journey to SQL Authority with Pinal Dave

Feeds:: Posts; Comments

SQL SERVER – Script to Find SQL Server on Network

April 13, 2007 by pinaldave

I manage lots of SQL Servers. Many times I forget how many server I have and what are their names. New servers are added frequently and old servers are replaced with powerful servers. I run following script to check if server is properly set up and announcing itself. This script requires execute permissions on XP_CMDShell.

CREATE TABLE #servers(sname VARCHAR(255)) INSERT #servers (sname) EXEC master..xp_CMDShell 'ISQL -L' DELETE FROM #servers WHERE sname='Servers:' OR sname IS NULL SELECT LTRIM(sname) FROM #servers DROP TABLE #servers

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 12 Comments

12 Responses

on July 10, 2007 at 11:12 pm | Reply tredinertok

Hi all!

Very interesting information! Thanks!

G’night
on July 13, 2007 at 7:38 am | Reply govokinolij

Hi all!

Looks good! Very useful, good stuff. Good resources here. Thanks much!

Bye
on October 31, 2007 at 3:39 am | Reply Jan Simon

Hey

Thanx a lot for this….it was really helpfull when i had to find wich servers on my network had SQL installed…My network has app. 3500 servers and this worked like a charm

Sincerely

Jan Simon
on November 14, 2007 at 8:01 pm | Reply Rajib Paul Choudhury

Thanks for this information.I need more information
if you provide me some query where I find all the server name,version name and service pack information
on January 15, 2008 at 9:16 am | Reply Shrinivas

Its very helpful and important information, while working in big organizations or corporations.

tks.
on November 10, 2008 at 11:05 am | Reply Rajni Kant Ranjan

Hi Dave,

I want one help that I have SQL Server List which containing 500 SQL Server Instance name. Now I have to fetch the below details:

SQL Server Version Installed
List of database on that server
by using T-SQL Script.

Do you have any idea or please help me?

Thanks n Regards
Rajni Kant Ranjan
on February 24, 2009 at 5:37 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Script to find SQL Server on Network [...]
on March 18, 2009 at 11:06 pm | Reply SQL SERVER - Find All Servers From Local Network - Using sqlcmd Journey to SQL Authority with Pinal Dave

[...] 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 [...]
on September 26, 2009 at 12:00 am | Reply Todd

CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers (sname)
EXEC master..xp_CMDShell ‘ISQL -L’

I get error on sql2005 … “‘ISQL’ is not recognized as an internal or external command,”

any idea why??
on October 5, 2009 at 8:56 pm | Reply Innocent Gumbo

Todd, Please use the SQLCMD or OSQL because ISQL was supported in SQL 2000 and its now obsolete!!

Just replace it.
on January 6, 2010 at 11:01 am | Reply Lakshmi Chethana

Awsome it worked for me
Use this for SQL server 2005

CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers (sname)
EXEC master..xp_CMDShell ‘OSQL -L’
DELETE
FROM #servers
WHERE sname=’Servers:’
OR sname IS NULL
SELECT LTRIM(sname)
FROM #servers
DROP TABLE #servers
on January 29, 2010 at 1:33 am | Reply Orlando

This is a good script for finding servers on the network. However, do you have a script to show which sql version and edition are on these servers? Please advise