So, you've added a linked server and need information about it. There must be

an easier way to fine information about your linked server and keep from walking to the server room.

This article will show you how to get some vital information from your linked with

T-SQL.

The stored procedure I use the most often is sp_linkedservers. This tells you

how many linked servers are out there and gives you information about them. It is especially handy if you have

obscure names for your linked servers. If you havn't established a linked server, you should only see one result, the local server's information.

You can also request a list of databases that are your linked servers. This is done

through the sp_catalogs stored procedure as shown below.

The procedure uses the @server_name variable, which is the name of the linked

server you are querying.

sp_catalogs @server_name = 'linkedservername'

CATALOG_NAME
-------------------------
master
model
msdb
Northwind
pubs
tempdb
(7 row(s) affected)
You now know which databases or catalogs are on the server.   The sp_tables_ex
 stored procedure does to tables what sp_catalogs does to databases.  This stored procedure
 will return a list of tables on any given server, in any given database.
 The only required parameter is @table_server, which is the linked server name.

EXEC sp_tables_ex @table_server = 'linkedservername'
This query though would result in a huge resultset that
 is basically unusable. If you run this query as above,
 you would receive over 80 records about the master database.
 In practice, you are going to want to see information about
 a specific catalog. You can do this by using the following variables:
Parameter
Use
@table_server
Name of the linked server you would like information about.
@table_catalog
Changes the default catalog for your query. If this option is not set,
 the default database for the user setup in the linked server is used.
@table_schema
Narrows the object owners you’d like to query. For example, dbo would
 be a table_schema. This is especially useful in other DBMSs.
@table_name
Find out information about a specific table.
@table_type
Will narrow your query down to a specific table type such as TABLE,
 SYSTEM TABLE, or VIEW.
Let’s try a more enhanced query:
EXEC sp_tables_ex
@table_server = 'linkservername',
@table_catalog='northwind',
@table_schema='dbo',
@table_name='Suppliers'
This time you will see a narrower result set.
TABLE_CAT        TABLE_SCHEM      TABLE_NAME     TABLE_TYPE
-----------      --------------   ----------     ----------
Northwind        dbo              Suppliers      TABLE     
(1 row(s) affected)
You can gather information about individual columns also by executing sp_columns_ex.
Again, with this stored procedure narrow your results by using specific parameters.
Parameter
Use
@table_server
Name of the linked server you would like information about.
@table_catalog
Changes the default catalog for your query. If this option is not set,
 the default database for the user setup in the linked server is used.
@table_schema
Narrows the object owners you’d like to query. For example, dbo would
 be a table_schema. This is especially useful in other DBMSs.
@table_name
Find out information about a specific table.
@table_type
Will narrow your query down to a specific table type such as TABLE,
 SYSTEM TABLE, or VIEW.

EXEC sp_columns_ex
@table_server = 'linkedservername',
@table_catalog = 'Northwind',
@table_name  = 'suppliers',
@table_schema = 'dbo',
@column_name='CompanyName'
This stored procedure will results are rather large and full of metadata.
In short, the metadata returned include:

 Column Names

 Column Nullability

Precision and scale

 Data type and length

Lastly, you can return security information about a table.  The sp_table_privileges_ex stored procedure
will return what permissions are established at the table level.  This procedure
becomes especially useful when you're trying to diagnose why you can't insert into a table.
To execute this procedure, use the following syntax:
EXEC sp_table_privileges_ex 
@table_server = 'linkservername', 
@table_catalog = 'Northwind',
@table_name  = 'Suppliers',
@table_schema = 'dbo'

TABLE_CAT  TABLE_SCHEM  TABLE_NAME GRANTOR GRANTEEPRIVILEGE
---------  ------------ ---------- ------- -----------------
Northwind  dbo          Suppliers  dbo     dbo  DELETE
Northwind  dbo          Suppliers  dbo     dbo   INSERT
Northwind  dbo          Suppliers  dbo     dboREFERENCES
Northwind  dbo          Suppliers  dbo     dboSELECT
(4 row(s) affected)
There are quite a few other stored procedures that you can experiment with
to gather metadata about your linked servers. The sp_primarykeys procedure will as it sounds return the primary
key column(s) for a table. The sp_foreignkeys procedure will return any dependencies that the table has. Finally, sp_indexes will return any indexes that are on a table.
Hopefully this article will save you a few trips to the server room or having to open up bulky tools.