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)
21 Comments. Leave new
Hi all!
Very interesting information! Thanks!
G’night
Hi all!
Looks good! Very useful, good stuff. Good resources here. Thanks much!
Bye
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
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
Its very helpful and important information, while working in big organizations or corporations.
tks.
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
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??
Todd, Please use the SQLCMD or OSQL because ISQL was supported in SQL 2000 and its now obsolete!!
Just replace it.
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
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
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
Hi, Its excellent … Thanks
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
Grove, your script when run in SQL 2008 gives lot of syntax errors.
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
Very Good Contant…
this website is very healpful to all sql developers and Programmers.
Thanks,
Akhil K. Jaiswal
.NET DEVELOPER
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
it is so interesting and provide the help as we want.
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.
sir my server is not listed. what should do to take it in list
sachin
Hi,
It’s not listing sql server instance which is configured on clustered instance. Is there any command to list those ?
Thanks in advance.