SQL Server – Linked Server – Metadata

Background

Needing to audit a SQL Server Instance to determine its list of Linked Servers.

Code

Outline

Query sys.servers system management view.

Query


select
         [servername]
            = serverproperty('servername')

        , [serverID]
            = tblSS.[server_id]

        , tblSS.[name]

        , tblSS.[product]

        , tblSS.[provider]

        , tblSS.[provider_string]

        , [dataSource]
            = tblSS.data_source

        , tblSS.[catalog]

        --, tblSS.*

        , [isLocal]
            = case
                when (tblSS.[server_id] =0) then 1
                else 0
              end	

        , [isLoopback]
            = case	

                when (tblSS.[server_id] =0) then 1

                when (
                        tblSS.data_source = cast(serverproperty('servername') as sysname)
                     ) then 1

                else 0
              end
        --, tblSS.*

from   sys.servers tblSS

Output

linkedServer.redwood.dev.20181129.0523PM.PNG

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s