SQL SERVER – Script to Find SQL Server on Network

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

Watch a 60 second video on this subject
[youtube=http://www.youtube.com/watch?v=8P5TuOg3PlA]

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

SQL Scripts, SQL Server Security, SQL Stored Procedure, SQL Utility
Previous Post
SQL SERVER – 2005 – Disable Triggers – Drop Triggers
Next Post
SQL SERVER – Stored Procedures Advantages and Best Advantage

Related Posts

21 Comments. Leave new

  • Hi all!

    Very interesting information! Thanks!

    G’night

    Reply
  • Hi all!

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

    Bye

    Reply
  • 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

    Reply
  • Rajib Paul Choudhury
    November 14, 2007 8:01 pm

    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

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

    tks.

    Reply
  • Rajni Kant Ranjan
    November 10, 2008 11:05 am

    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

    Reply
  • 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??

    Reply
  • Innocent Gumbo
    October 5, 2009 8:56 pm

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

    Just replace it.

    Reply
  • Lakshmi Chethana
    January 6, 2010 11:01 am

    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

    Reply
    • Hi ,
      I want to run this query on 4 diff enviornment so if you explains steps it would be bettoer to understand for me.
      thanks

      Reply
  • 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

    Reply
  • Hi, Its excellent … Thanks

    Reply
  • I modified his script to the following to pull the version information. The one problem with this is if you are trying to connect to SQLExpress or something like that you will often get an error. So I brute forced around this but I’m sure there is a nicer way to do it.

    set nocount on
    CREATE TABLE #servers(Server_Name VARCHAR(255),
    Server_Version nvarchar(255))

    INSERT #servers (Server_Name)
    EXEC master..xp_CMDShell ‘sqlcmd -Lc’

    DELETE FROM #servers
    WHERE Server_Name = ‘Servers:’
    OR Server_Name IS NULL
    OR Server_Name = ”

    DECLARE @tempdata TABLE (scratch sql_variant)

    DECLARE @version nvarchar(255),
    @cmd nvarchar(1000),
    @server varchar(255)

    DECLARE ServerList CURSOR FAST_FORWARD
    FOR SELECT server_name FROM #servers

    OPEN ServerList

    FETCH NEXT FROM ServerList INTO @server

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT ‘Processing: ‘ + @server

    IF @server IN (‘servera’,’serverb’)
    BEGIN
    UPDATE #servers SET Server_Version = CASE @server
    WHEN ‘OLDDATA’ THEN ‘FailOver for Flowserver’
    ELSE ‘UNABLE TO CONNECT TO SERVER’
    END
    WHERE Server_Name = @server
    END
    ELSE
    BEGIN
    SELECT @cmd = ‘SET NOCOUNT ON;SELECT * FROM OPENROWSET(”SQLNCLI”, ”Server=’ + @server + ‘;’
    + ‘uid=’ + CASE @server
    WHEN ‘server1’ THEN ‘usera’
    ELSE ‘userb’
    END + ‘;’
    + ‘pwd=’+ CASE @server
    WHEN ‘server1’ THEN ‘pass1’
    WHEN ‘server2’ THEN ‘pass2’
    ELSE ‘commonpass’
    END + ‘;”’
    + ‘, ”select @@version”)’
    INSERT INTO @tempdata (scratch) EXEC (@cmd)
    SELECT @version = convert(nvarchar(255),scratch) from @tempdata
    DELETE FROM @tempdata

    UPDATE #servers SET Server_Version = @version WHERE Server_Name = @server
    END

    FETCH NEXT FROM ServerList INTO @server
    END

    CLOSE ServerList
    DEALLOCATE ServerList

    select * from #servers

    Reply
  • Innocent Gumbo
    August 19, 2010 1:01 am

    Orlando, please try the SELECT SERVERPROPERTY(‘Edition’)
    or SELECT @@Version
    but Server Properties are more smarter – you can get to the Servive Pack level using SERVERPROPERTY, check Books online for more properties

    Thanks

    Reply
  • Akhil Kumar Jaiswal
    September 16, 2010 1:44 pm

    Very Good Contant…
    this website is very healpful to all sql developers and Programmers.

    Thanks,
    Akhil K. Jaiswal
    .NET DEVELOPER

    Reply
  • Hi,
    its worked to get server names but can we get all the databases created under these server as well.
    Please let me know if anybody has any clue.

    Thanks in advance.

    Regards.
    Laldhar

    Reply
  • Abhishek Shakya
    January 24, 2011 12:08 pm

    it is so interesting and provide the help as we want.

    Reply
  • Vasant Jagtap
    March 29, 2011 7:36 pm

    Hi,

    It give this error because we not enable xp_cmdshell, any other T-SQL script?

    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
    SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

    Reply
  • sir my server is not listed. what should do to take it in list
    sachin

    Reply
  • Hi,

    It’s not listing sql server instance which is configured on clustered instance. Is there any command to list those ?

    Thanks in advance.

    Reply

Leave a Reply