Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Viewing Linked Servers' Meta Data

By Brian Knight,

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.
Total article views: 7612 | Views in the last 30 days: 11
 
Related Articles
FORUM

Information about reports ran

Information about reports ran

FORUM

SQL Server 2005 - Repopulate Full Text Catalog After Migration

Repopulate Full Text Catalog

FORUM

required the way to update the System Catalog in Sql Server 2005

System Catalog updation process in sql server 2005.....

FORUM

Sql Server Full-Text catalog/index is empty

Sql Server Full-Text catalog is not populating

FORUM

need help about procedure

procedure

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones