Database names from multiple SQL servers
2 answers
This gives all databases of the current server
EXEC sp_databases
If you know another ips server, you can use sp_addlinkedserver to link them and get the results you want.
EDIT: Below is a complete answer for you as described above but using sys.databases
DECLARE @T AS TABLE (dbName VARCHAR(250),ipAddress VARCHAR(20))
DECLARE @SERVERS AS TABLE (Id INT IDENTITY(1,1),serverIp VARCHAR(20),
rmUser VARCHAR(20),rmPassword VARCHAR(20))
DECLARE @ipAdd AS VARCHAR(20),@user AS VARCHAR(20), @pw AS VARCHAR(20),@rowCount AS INT
SELECT @rowCount = 1
--ADD THE LIST OF SERVERS WITH LOGIN USER NAME AND PASSWORDS HERE
INSERT INTO @SERVERS (serverIp,rmUser,rmPassword)
VALUES ('serverIp1','rmuser1','rmpassword1'), ('serverIp2','rmuser2','rmpassword2')
WHILE EXISTS(SELECT Id FROM @SERVERS WHERE Id = @rowCount)
BEGIN
SELECT @ipAdd = serverIp, @user= rmUser, @pw = rmPassword
FROM @SERVERS WHERE Id = @rowCount
--Link the server and add login
EXEC sp_addlinkedserver @ipAdd,N'SQL Server';
EXEC sp_addlinkedsrvlogin @rmtsrvname=@ipAdd,
@useself='false',
@rmtuser=@user,
@rmtpassword=@pw
--INSERT results into a temp view and then into @T
EXEC('CREATE VIEW vTemp AS SELECT name FROM ['+@ipAdd+'].master.sys.databases')
INSERT INTO @T SELECT name,@ipAdd FROM vTemp
--Drop view
DROP VIEW vTemp
--Drop login and link to the remort server
EXEC sp_droplinkedsrvlogin @ipAdd, NULL
EXEC sp_dropserver @ipAdd, NULL;
SELECT @rowCount = @rowCount +1
END
--FINALLY YOUR RESULTS
SELECT * FROM @T
0
source to share
You can get information about the database using.
SELECT * from sys.databases
If you want to know this from other servers, in pure t-sql you need to link the server.
Additional Information
Update after comment
You say that you cannot link servers. Another alternative is to create a CLR.NET object. and insert them into the server.
Than you can connect to other servers without tying them together and execute SQL statements.
Additional Information
+4
source to share