Viewing Linked Servers' Meta Data

,

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 GRANTEE	PRIVILEGE
---------  ------------ ---------- ------- --------	---------
Northwind  dbo          Suppliers  dbo     dbo  	DELETE
Northwind  dbo          Suppliers  dbo     dbo   	INSERT
Northwind  dbo          Suppliers  dbo     dbo		REFERENCES
Northwind  dbo          Suppliers  dbo     dbo		SELECT
(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.

Rate

Share

Share

Rate