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

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

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

    Sincerely

    Jan Simon

    Like

  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

    Like

  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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  13. Hi,

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

    Thanks in advance.

    Like

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