Use SQL to find all the available SQL server in the network
Posted on 1/23/2008 6:24:37 PM
in #SQL SERVER
Hi,
An interesting question asked to me by one of my colleague quite some days back. How do we find all the SQL server running in the network in SQL? Well the requirement was unique, but the way to do it is pretty simple.
All we need to do is use the XP_CMDShell stored procedure in the master database with a parameter 'OSQL -L'. So the full code for the same is
CREATE PROCEDURE dbo.ListOFLocalServer
AS
BEGIN
CREATE TABLE #tempserverTable(servername VARCHAR(255))
INSERT #tempserverTable EXEC master..XP_CMDShell 'OSQL -L'
DELETE # tempserverTable WHERE servername ='Servers:'
SELECT LTRIM(servername) FROM #tempserverTable WHERE servername!= 'NULL'
DROP TABLE #tempserverTable
END
Note the stored procedure will find only those who are broadcasting there existence and also requires you to have XP_CMDShell permission.
Vikram
|