Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.servers

  No additional text.


Syntax
CREATE VIEW sys.servers AS
	SELECT s.id AS server_id,
		s.name, s.product, s.provider,
		data_source = convert(nvarchar(4000), v.value),
		location = convert(nvarchar(4000), l.value),
		provider_string = convert(nvarchar(4000), LinkedServerProperty(s.name, 'ProviderString')),
		s.catalog,
		s.connecttimeout AS connect_timeout,
		s.querytimeout AS query_timeout,
		sysconv(bit, s.status & 4) AS is_linked,				-- SRV_NORPCMAP
		sysconv(bit, s.status & 1) AS is_remote_login_enabled,	-- SRV_RPCIN
		sysconv(bit, s.status & 2) AS is_rpc_out_enabled,		-- SRV_RPCOUT
		sysconv(bit, s.status & 8) AS is_data_access_enabled,	-- SRV_DATA
		sysconv(bit, s.status & 16) AS is_collation_compatible,	-- SRV_COLLATECOMPAT
		sysconv(bit, s.status & 64) AS uses_remote_collation,	-- SRV_USERMTCOLLATE
		convert(sysname, collationpropertyfromid(s.cid,'name')) AS collation_name,
		sysconv(bit, s.status & 128) AS lazy_schema_validation,	-- SRV_LAZYSCHCHECK
		sysconv(bit, s.status & 32) AS is_system,				-- SRV_SYSTEM
		sysconv(bit, s.status & 256) AS is_publisher,			-- SRV_PUB
		sysconv(bit, s.status & 0x200) AS is_subscriber,		-- SRV_SUB
		sysconv(bit, s.status & 0x400) AS is_distributor,		-- SRV_DIST
		sysconv(bit, s.status & 0x800) AS is_nonsql_subscriber,	-- SRV_NONSQLSUB
		~(sysconv(bit, s.status & 0x1000)) AS is_remote_proc_transaction_promotion_enabled,	-- SRV_NODTCPROMOFORRPC
		s.modate AS modify_date							-- SRV80_USERMTCOLLATE
	FROM master.sys.sysxsrvs s
	LEFT JOIN sys.sysobjvalues v ON v.valclass = 25 AND v.objid = s.id AND v.subobjid = 1 AND v.valnum = 0	-- SRV_DATASRC
	LEFT JOIN sys.sysobjvalues l ON l.valclass = 25 AND l.objid = s.id AND l.subobjid = 2 AND l.valnum = 0	-- SRV_LOCATION
	WHERE s.id = 0 OR has_access('SR', s.id) = 1

 
Last revision 2008RTM
See also

  dm_io_cluster_shared_drives (View)
sp_adddistributiondb (Procedure)
sp_adddistributor (Procedure)
sp_addlinkedsrvlogin (Procedure)
sp_addmergesubscription (Procedure)
sp_addsubscriber_schedule (Procedure)
sp_add_job (Procedure)
sp_add_jobschedule (Procedure)
sp_add_jobserver (Procedure)
sp_add_jobstep_internal (Procedure)
sp_add_schedule (Procedure)
sp_add_targetsvrgrp_member (Procedure)
sp_apply_job_to_targets (Procedure)
sp_attachsubscription (Procedure)
sp_attach_schedule (Procedure)
sp_changedistpublisher (Procedure)
sp_changedistributiondb (Procedure)
sp_changedistributor_password (Procedure)
sp_changedistributor_property (Procedure)
sp_changereplicationserverpasswords (Procedure)
sp_change_subscription_properties (Procedure)
sp_copysubscription (Procedure)
sp_delete_all_msx_jobs (Procedure)
sp_delete_job (Procedure)
sp_delete_jobschedule (Procedure)
sp_delete_jobserver (Procedure)
sp_delete_jobstep (Procedure)
sp_delete_operator (Procedure)
sp_delete_schedule (Procedure)
sp_delete_targetserver (Procedure)
sp_delete_targetsvrgrp_member (Procedure)
sp_detach_schedule (Procedure)
sp_distagentstatus (Procedure)
sp_dropdistpublisher (Procedure)
sp_dropdistributiondb (Procedure)
sp_dropdistributor (Procedure)
sp_droplinkedsrvlogin (Procedure)
sp_dropmergepullsubscription (Procedure)
sp_dropremotelogin (Procedure)
sp_dropserver (Procedure)
sp_enlist_tsx (Procedure)
sp_enumoledbdatasources (Procedure)
sp_generate_target_server_job_assignment_sql (Procedure)
sp_get_composite_job_info (Procedure)
sp_get_distributor (Procedure)
sp_get_Oracle_publisher_metadata (Procedure)
sp_get_sqlagent_properties (Procedure)
sp_helpdistpublisher (Procedure)
sp_helpdistributiondb (Procedure)
sp_helpdistributor (Procedure)
sp_helplinkedsrvlogin (Procedure)
sp_helpremotelogin (Procedure)
sp_helpserver (Procedure)
sp_helpsubscriptionerrors (Procedure)
sp_help_alert (Procedure)
sp_help_downloadlist (Procedure)
sp_help_jobhistory (Procedure)
sp_help_jobhistory_full (Procedure)
sp_help_jobhistory_sem (Procedure)
sp_help_jobhistory_summary (Procedure)
sp_help_jobserver (Procedure)
sp_help_targetserver (Procedure)
sp_help_targetservergroup (Procedure)
sp_IHaddpublication (Procedure)
sp_IHaddpublisher (Procedure)
sp_IHadd_sync_command (Procedure)
sp_IHarticlecolumn (Procedure)
sp_IHarticleview (Procedure)
sp_IHarticle_validation (Procedure)
sp_IHCheckPublisherLogin (Procedure)
sp_IHdroparticle (Procedure)
sp_IHdroppublisher (Procedure)
sp_IHgetPublicationInfo (Procedure)
sp_IHgetPublisherInfo (Procedure)
sp_IHget_loopback_detection (Procedure)
sp_IHhelppublication (Procedure)
sp_IHmarksubscriptionvalidation (Procedure)
sp_IHpublication_validation (Procedure)
sp_IHreplflush (Procedure)
sp_IHreplincrementlsn (Procedure)
sp_IH_LR_GetCacheData (Procedure)
sp_linkedservers (Procedure)
sp_linkedservers_rowset (Procedure)
sp_linkedservers_rowset2 (Procedure)
sp_link_publication (Procedure)
sp_manage_jobs_by_login (Procedure)
sp_MSaddserver_internal (Procedure)
sp_MSadd_anonymous_agent (Procedure)
sp_MSadd_compensating_cmd (Procedure)
sp_MSadd_distribution_agent (Procedure)
sp_MSadd_distribution_history (Procedure)
sp_MSadd_logreader_agent (Procedure)
sp_MSadd_logreader_history (Procedure)
sp_MSadd_merge_agent (Procedure)
sp_MSadd_merge_anonymous_agent (Procedure)
sp_MSadd_merge_history (Procedure)
sp_MSadd_qreader_history (Procedure)
sp_MSadd_repl_alert (Procedure)
sp_MSadd_repl_command (Procedure)
sp_MSadd_snapshot_agent (Procedure)
sp_MSadd_snapshot_history (Procedure)
sp_MSadd_subscriber_info (Procedure)
sp_MSadd_subscriber_schedule (Procedure)
sp_MSadd_subscription (Procedure)
sp_MSadd_subserver (Procedure)
sp_MSadd_tracer_token (Procedure)
sp_MSagent_stethoscope (Procedure)
sp_MSbrowsesnapshotfolder (Procedure)
sp_MSchangedynamicsnapshotjobatdistributor (Procedure)
sp_MSchangedynsnaplocationatdistributor (Procedure)
sp_MSchangepublicationlinkpasswords (Procedure)
sp_MSchangerepltablepasswords (Procedure)
sp_MSchange_distribution_agent_properties (Procedure)
sp_MSchange_logreader_agent_properties (Procedure)
sp_MSchange_merge_agent_properties (Procedure)
sp_MSchange_snapshot_agent_properties (Procedure)
sp_MSchange_subscription_dts_info (Procedure)
sp_MScheck_Jet_Subscriber (Procedure)
sp_MScheck_snapshot_agent (Procedure)
sp_MScheck_subscription_count_internal (Procedure)
sp_MScleanup_subscription_distside_entry (Procedure)
sp_MScreate_dist_tables (Procedure)
sp_MSdelete_tracer_history (Procedure)
sp_MSdistributionagentjobcontrol (Procedure)
sp_MSdistribution_counters (Procedure)
sp_MSdist_adjust_identity (Procedure)
sp_MSDropCanClearSubscriberStatus (Procedure)
sp_MSdrop_6x_publication (Procedure)
sp_MSdrop_distribution_agent (Procedure)
sp_MSdrop_distribution_agentid (Procedure)
sp_MSdrop_dynamic_snapshot_agent (Procedure)
sp_MSdrop_logreader_agent (Procedure)
sp_MSdrop_merge_agent (Procedure)
sp_MSdrop_merge_subscription (Procedure)
sp_MSdrop_publication (Procedure)
sp_MSdrop_snapshot_agent (Procedure)
sp_MSdrop_subscriber_info (Procedure)
sp_MSdrop_subscription (Procedure)
sp_MSdrop_subserver (Procedure)
sp_MSdynamicsnapshotjobexistsatdistributor (Procedure)
sp_MSenum3rdpartypublications (Procedure)
sp_MSenumallpublications (Procedure)
sp_MSenumdistributionagentproperties (Procedure)
sp_MSenumtranpublications (Procedure)
sp_MSenum_distribution (Procedure)
sp_MSenum_logreader (Procedure)
sp_MSenum_merge (Procedure)
sp_MSenum_merge_agent_properties (Procedure)
sp_MSenum_merge_subscriptions (Procedure)
sp_MSenum_merge_subscriptions_90_publication (Procedure)
sp_MSenum_merge_subscriptions_90_publisher (Procedure)
sp_MSenum_replication_status (Procedure)
sp_MSenum_replsqlqueues (Procedure)
sp_MSenum_snapshot (Procedure)
sp_MSenum_subscriptions (Procedure)
sp_MSfetchAdjustidentityrange (Procedure)
sp_MSforce_drop_distribution_jobs (Procedure)
sp_MSget_agent_names (Procedure)
sp_MSget_last_transaction (Procedure)
sp_MSget_max_used_identity (Procedure)
sp_MSget_oledbinfo (Procedure)
sp_MSget_publisher_rpc (Procedure)
sp_MSget_repl_commands (Procedure)
sp_MSget_shared_agent (Procedure)
sp_MShelpdynamicsnapshotjobatdistributor (Procedure)
sp_MShelplogreader_agent (Procedure)
sp_MShelpsnapshot_agent (Procedure)
sp_MShelptracertokenhistory (Procedure)
sp_MShelptracertokens (Procedure)
sp_MShelp_article (Procedure)
sp_MShelp_distribution_agentid (Procedure)
sp_MShelp_merge_agentid (Procedure)
sp_MShelp_publication (Procedure)
sp_MShelp_repl_agent (Procedure)
sp_MShelp_snapshot_agentid (Procedure)
sp_MShelp_subscription (Procedure)
sp_MShelp_subscription_status (Procedure)
sp_MSIfExistsRemoteLogin (Procedure)
sp_MSIfExistsSubscription (Procedure)
sp_MSispublicationqueued (Procedure)
sp_MSmergeagentjobcontrol (Procedure)
sp_MSmerge_log_identity_range_allocations (Procedure)
sp_MSmerge_subscriptions_upgrade (Procedure)
sp_MSprocesslogshipmonitorlink (Procedure)
sp_MSprocesspublisherlink (Procedure)
sp_MSpublishdb (Procedure)
sp_MSrefreshmqtosql (Procedure)
sp_MSrefresh_anonymous (Procedure)
sp_MSreinit_subscription (Procedure)
sp_MSremovedbreplication_internal (Procedure)
sp_MSreplagentjobexists (Procedure)
sp_MSrepl_adddistpublisher (Procedure)
sp_MSrepl_addpublication (Procedure)
sp_MSrepl_addpushsubscription_agent (Procedure)
sp_MSrepl_addsubscriber (Procedure)
sp_MSrepl_addsubscription (Procedure)
sp_MSrepl_agentstatussummary (Procedure)
sp_MSrepl_browsesnapshotfolder (Procedure)
sp_MSrepl_changearticlecolumndatatype (Procedure)
sp_MSrepl_changesubscriber (Procedure)
sp_MSrepl_changesubscriber_schedule (Procedure)
sp_MSrepl_changesubstatus (Procedure)
sp_MSrepl_check_server (Procedure)
sp_MSrepl_DistDBPALAccess (Procedure)
sp_MSrepl_distributionagentstatussummary (Procedure)
sp_MSrepl_droppublication (Procedure)
sp_MSrepl_dropsubscriber (Procedure)
sp_MSrepl_enumpublications (Procedure)
sp_MSrepl_enumsubscriptions (Procedure)
sp_MSrepl_getdistributorinfo (Procedure)
sp_MSrepl_getsubscription_status_hsnapshot (Procedure)
sp_MSrepl_get_Oracle_publisher_metadata (Procedure)
sp_MSrepl_helpsubscriberinfo (Procedure)
sp_MSrepl_linkedservers_rowset (Procedure)
sp_MSrepl_mergeagentstatussummary (Procedure)
sp_MSrepl_refresh_heterogeneous_publisher (Procedure)
sp_MSrepl_snapshot_helppublication (Procedure)
sp_MSrepl_startup_internal (Procedure)
sp_MSrepl_subscription_rowset (Procedure)
sp_MSrepl_testadminconnection (Procedure)
sp_MSrepl_validatecache (Procedure)
sp_MSreset_subscription (Procedure)
sp_MSreset_transaction (Procedure)
sp_MSrestoredbreplication (Procedure)
sp_MSsetupnosyncsubwithlsnatdist (Procedure)
sp_MSSharedFixedDisk (Procedure)
sp_MSsnapshotagentjobcontrol (Procedure)
sp_MStran_is_snapshot_required (Procedure)
sp_MSupdate_subscription (Procedure)
sp_MSupgrade_distdb_security_metadata (Procedure)
sp_MSupgrade_heterogeneous_subscriber_connectinfo (Procedure)
sp_MSvalidate_distpublisher (Procedure)
sp_msx_defect (Procedure)
sp_msx_enlist (Procedure)
sp_multi_server_job_summary (Procedure)
sp_ORAaddpublisher (Procedure)
sp_ORACheckAdminPrivileges (Procedure)
sp_ORACheckLoopbackSupport (Procedure)
sp_ORACheckSourceTable (Procedure)
sp_ORAdroppublisher (Procedure)
sp_ORAenumarticlecolumninfo (Procedure)
sp_ORAenumpublishertables (Procedure)
sp_ORAGetTablespaceInfo (Procedure)
sp_ORAgetversion (Procedure)
sp_ORAhelpXactBatching (Procedure)
sp_ORAhelpXactSetJob (Procedure)
sp_ORASchemaFilter (Procedure)
sp_ORASetXactBatching (Procedure)
sp_ORAvalidatecache (Procedure)
sp_ORAValidateRowFilter (Procedure)
sp_ORAXactSetJob (Procedure)
sp_post_msx_operation (Procedure)
sp_refreshreplsysservers (Procedure)
sp_removedistpublisherdbreplication (Procedure)
sp_removesrvreplication (Procedure)
sp_remove_job_from_targets (Procedure)
sp_replicationdboption (Procedure)
sp_replmonitorchangepublicationthreshold (Procedure)
sp_replmonitorhelpmergesubscriptionmoreinfo (Procedure)
sp_replmonitorhelppublicationthresholds (Procedure)
sp_replmonitorhelpsubscription (Procedure)
sp_replmonitorsubscriptionpendingcmds (Procedure)
sp_requestpeertopologyinfo (Procedure)
sp_resync_targetserver (Procedure)
sp_serveroption (Procedure)
sp_setnetname (Procedure)
sp_sqlagent_probe_msx (Procedure)
sp_sqlagent_refresh_job (Procedure)
sp_start_job (Procedure)
sp_stop_job (Procedure)
sp_syscollector_delete_jobs (Procedure)
sp_sysmanagement_add_shared_registered_server (Procedure)
sp_sysmanagement_delete_shared_registered_server (Procedure)
sp_sysmanagement_move_shared_registered_server (Procedure)
sp_sysmanagement_rename_shared_registered_server (Procedure)
sp_sysmanagement_update_shared_registered_server (Procedure)
sp_sysutility_ucp_initialize_mdw (Procedure)
sp_target_server_summary (Procedure)
sp_update_agent_profile (Procedure)
sp_update_job (Procedure)
sp_update_jobschedule (Procedure)
sp_update_jobstep (Procedure)
sp_update_operator (Procedure)
sp_update_schedule (Procedure)
sp_upgrade_log_shipping (Procedure)
sp_verify_alert (Procedure)
sp_verify_job (Procedure)
sp_verify_jobstep (Procedure)
sp_verify_notification (Procedure)
sp_verify_schedule_identifiers (Procedure)
sp_vupgrade_heterogeneous_publishers (Procedure)
sp_vupgrade_mergetables (Procedure)
sp_vupgrade_publisher (Procedure)
sp_vupgrade_publisherdb (Procedure)
sysjobs_view (View)
sysmaintplan_plans (View)
sysmanagement_shared_registered_servers (View)
sysmanagement_shared_server_groups (View)
sysoriginatingservers_view (View)
sysschedules_localserver_view (View)
sysservers (View)
systargetservers_view (View)
sysutility_ucp_instances (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash