Sometimes you may wants to retrieve a linked server configuration details. Following T-SQL statement retrieves information such as Hostname, SQL Instance name, Server Edition (32 bit or 64 bit), IP address, Port , ServicePack and cluster configuration for a preconfigured Linked server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @SQL NVARCHAR(MAX); DECLARE @LinkedServerName NVARCHAR(128) SET @LinkedServerName='Linked Server Name' -- Set your linked server name here SELECT @SQL = 'SELECT (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT @@ServerName'')) as [Server_Name],' SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT SERVERPROPERTY(''''MachineName'''')'')) as [MachineName],' SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT SERVERPROPERTY(''''InstanceName'''')'')) AS [InstanceName],' SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT SERVERPROPERTY(''''Edition'''')'')) AS [Edition] ,' SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT SERVERPROPERTY(''''ProductLevel'''')'')) AS [ProductLevel] , ' SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT Case SERVERPROPERTY(''''IsClustered'''') WHEN 1 then ''''CLUSTERED'''' ELSE ''''STANDALONE'''' end '')) As [IsClustered],' SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT @@VERSION'')) AS [VersionNumber],' SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT CONNECTIONPROPERTY(''''local_net_address'''')'')) AS [Server IP Address], ' SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT CONNECTIONPROPERTY(''''local_tcp_port'''')'')) AS [local_tcp_port],' SELECT @SQL = @SQL +' create_date AS [Server Started],' SELECT @SQL = @SQL +' CAST(DATEDIFF(s, create_date, GETDATE()) / 86400.0 as INT) AS [Server is running for] ' SELECT @SQL = @SQL +' FROM ['+@LinkedServerName+'].master.sys.databases WHERE name = ''tempdb'' ORDER BY [Server_Name];' EXEC(@SQL) |
Leave a Comment