sp_helpserver (T-SQL)

Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the server’s network name, the server’s replication status, and the server’s identification number.

Syntax

sp_helpserver [[@server =] 'server']
    [,[@optname =] 'option']
    [,[@show_topology =] 'show_topology']

Arguments

[@server =] 'server'

Is the server about which information is reported. When no server is supplied, reports about all servers in master.dbo.sysservers. server is sysname, with a default of NULL.

[@optname =] 'option'

Is the option describing the server. option is varchar(35), with a default of NULL, and must be one of these values.

 

Value	Description
collation compatible	Affects the Distributed Query execution against linked servers. If this option is set to true, Microsoft SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order).
data access	Enables and disables a linked server for distributed query access.
dist	Distributor.
dpub	Remote Publisher to this Distributor.
pub	Publisher.
rpc	Enables RPC from the given server.
rpc out	Enables RPC to the given server.
sub	Subscriber.
system	For internal use only.

[@show_topology =] 'show_topology'

Is the relationship of the given server to other servers. show_topology is varchar(1), with a default of NULL. If show_topology is not equal to t or is NULL, sp_helpserver returns the server’s name, network name, status, and ID. Otherwise, sp_helpserver returns the server’s name, network name, status, ID, and topology information.

Return Code Values

0 (success) or 1 (failure).

Result Sets

Remarks

A server can have more than one status.

Permissions

Execute permissions default to the public role.

Examples

A. Display information about all servers

This example displays information about all servers (sp_helpserver with no parameters).

USE master

EXEC sp_helpserver

B. Display information about a specific server

This example displays all information about the SEATTLE2 server.

USE master

EXEC sp_helpserver 'SEATTLE2'

See Also