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'
WHERE sname='Servers:'
OR sname IS NULL
FROM #servers
DROP TABLE #servers

Watch a 60 second video on this subject

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

About these ads

24 thoughts on “SQL SERVER – Script to Find SQL Server on Network

  1. 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


    Jan Simon

  2. 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

  3. 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

  4. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER - Find All Servers From Local Network - Using sqlcmd Journey to SQL Authority with Pinal Dave

  6. 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??

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

    Just replace it.

  8. 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’
    FROM #servers
    WHERE sname=’Servers:’
    OR sname IS NULL
    SELECT LTRIM(sname)
    FROM #servers
    DROP TABLE #servers

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

  9. 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

  10. 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)

    FOR SELECT server_name FROM #servers

    OPEN ServerList

    FETCH NEXT FROM ServerList INTO @server

    PRINT ‘Processing: ‘ + @server

    IF @server IN (‘servera’,’serverb’)
    UPDATE #servers SET Server_Version = CASE @server
    WHEN ‘OLDDATA’ THEN ‘FailOver for Flowserver’
    WHERE Server_Name = @server
    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

    FETCH NEXT FROM ServerList INTO @server

    CLOSE ServerList
    DEALLOCATE ServerList

    select * from #servers

  11. 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


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

    Akhil K. Jaiswal

  13. 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.


  14. 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.

  15. Hi,

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

    Thanks in advance.

  16. Pingback: SQL SERVER – Get SQL Server Version and Edition Information – SQL in Sixty Seconds #043 – Video « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s