Presents your 
SQL SERVER E-NEWSLETTER for November 21, 2002

<------------------------------------------->

Retrieve information regarding linked servers

There are several system procedures that retrieve linked server
metadata. We'll discuss the following list of system procedures and 
demonstrate their use through the accompanying sample script:

* sp_linkedservers
* sp_catalogs
* sp_tables_ex
* sp_columns_ex
* sp_foreignkeys
* sp_primarykeys
* sp_indexes

The first system procedure, sp_linkedservers, returns a list of all the
linked servers that are available on the local server.

The system procedure sp_catalogs will list all the accessible databases
on the linked servers. When you're creating the linked server, a catalog
is specified in the process. In the script below, the NorthWind database
is used for the catalog or database of the linked server.

The system procedure sp_tables_ex will list all the names of the tables
for the catalog that was denoted when the linked server was added. When
creating a linked server, you need to list a catalog, which is a database
name, for Microsoft SQL server. The tables will be associated with this
database.

For the remaining system procedures, the output depends on the catalog
name listed for the linked server:

* sp_columns_ex--Will return the list of columns of the tables of the linked server.

* sp_foreignkeys--Will return a list of all the foreign keys of the linked server.

* sp_primarykeys--Will return a list of all the primary keys of the linked server.

* sp_indexes--Will return a list of all the indexes of the linked server.

The following script uses the @@SERVERNAME global variable as a local
variable for ease of use. You can replace @@SERVERNAME with a server name
other than the global variable as long as it's on the network for SQL
server to detect. The local variable in the script, @MyLinkServer, is
assigned a name of 'MyLinkedServer', but it can be assigned to any 
name. This name can be treated as an alias-linked server name for the 
linked server:

SET NOCOUNT ON
GO
DECLARE @MyLinkServer sysname, @MyServer sysname
SET @MyLinkServer = 'MyLinkedServer'
SET @MyServer = @@SERVERNAME
--Drop linked server if exists
IF EXISTS (SELECT * FROM master..sysservers
                   WHERE srvname = @MyLinkServer)
BEGIN
  EXEC sp_dropserver @server = @MyLinkServer,
      @droplogins = 'droplogins'
END
--Add linked server
EXEC sp_addlinkedserver
  @server = @MyLinkServer ,
           -- server alias name used by API SQL Server code
  @srvproduct = '' ,      
           -- blank string=Does not use SQL Server linked server,
           -- but OLEDB provider for SQL Server
  @provider = 'SQLOLEDB' ,    
           -- OLEDB provider for SQL Server
  @datasrc = @MyServer ,   
           -- Actual server name; server alias name for SS2K using SS
           -- client network utilities.
  @catalog = 'NorthWind'   
           -- Optional database.  
           -- Depends on what databases you may use based on remote
           -- login permissions.
--Add ability to call remote procedures
EXEC sp_serveroption @server = @MyLinkServer,
   @optname = 'rpc' ,@optvalue = 'true'
--Add linked server remote logins to imperssonate
EXEC sp_addlinkedsrvlogin
  @rmtsrvname = @MyLinkServer ,   -- linked server alias name
  @useself = 'true'        -- true=impersonate
--List linked servers
EXEC sp_linkedservers
--List databases that can be accessed
EXEC sp_catalogs @server_name = @MyLinkServer
--List the tables in the linked server
EXEC sp_tables_ex @table_server = @MyLinkServer
--List the table columns in the linked servver
EXEC sp_columns_ex @table_server = @MyLinkServer
--List foreign keys of linked server
EXEC sp_foreignkeys @table_server = @MyLinkServer
--List primary keys of linked server
EXEC sp_primarykeys  @table_server = @MyLinkServer
--List indexes of linked server
EXEC sp_indexes @table_server = @MyLinkServer
GO

----------------------------------------