SQL Server – Linked Server – Identify Dependencies and Usage

Introduction

We are slated for a major database upgrade and I am trying to document our current DB Environment.  One of the areas that used to be a bit difficult to fully document are Linked Server Dependencies.

Background

There is likely a bit of curmudgeon and shall we say general grumpiness in writers.  Be they Political reporters, crime beat writers, or even the Skip Bayless of the world.

 

Document Linked Server Usage

So again I will rather write about the difficulty of finding SQL Linked Server Object Dependencies, but Microsoft has closed that trap.

And, so here I am and I might as well give it to you straight.

If you ‘re running MS SQL Server v2008 or later and you need to find objects that reference Linked Server, then issue a query that is similar to:


SELECT 

	  schema_name(tblObjectReferencing.schema_id) as referencingSchema
	, OBJECT_NAME(tblSQLExpressionDependency.referencing_id) AS referencingObject
	, tblObjectReferencing.type_desc as referencingType
	, referenced_server_name AS referencedServer
	, referenced_database_name AS referencedDatabase
	, referenced_schema_name AS referencedSchema
	, referenced_entity_name as referencedEntity

FROM sys.sql_expression_dependencies tblSQLExpressionDependency

    left outer join sys.objects as tblObjectReferencing

      on   tblSQLExpressionDependency.referencing_id = tblObjectReferencing.object_id

    left outer join sys.objects as tblObjectDependency

       on   tblSQLExpressionDependency.referenced_schema_name 
               = schema_name(tblObjectDependency.schema_id)
       and  tblSQLExpressionDependency.referenced_entity_name 
             = tblObjectDependency.name

where tblSQLExpressionDependency.referenced_server_name is not null

The star of the show is of course the sys.sql_expression_dependencies DMV.

If you want to iterate this information for a specific database object, you can rest on the sys.dm_sql_referenced_entities dynamic management view.


Syntax

SELECT 

	  tblSQLReferencedEntity.referenced_server_name as referencedServer
	, tblSQLReferencedEntity.referenced_database_name as referencedDatabase
	, tblSQLReferencedEntity.referenced_schema_name as referencedSchema
	, tblSQLReferencedEntity.referenced_entity_name as referencedEntity

FROM sys.dm_sql_referenced_entities([full-object-name], [object-type]) 
        tblSQLReferencedEntity

where  tblSQLReferencedEntity.referenced_server_name is not null


Sample


SELECT 

	  tblSQLReferencedEntity.referenced_server_name as referencedServer
	, tblSQLReferencedEntity.referenced_database_name as referencedDatabase
	, tblSQLReferencedEntity.referenced_schema_name as referencedSchema
	, tblSQLReferencedEntity.referenced_entity_name as referencedEntity

FROM sys.dm_sql_referenced_entities('dbo.usp_BuildCustomerTree', 'Object') 
          tblSQLReferencedEntity

where  tblSQLReferencedEntity.referenced_server_name is not null

Please keep in mind that you have to pass in a valid object name and object class. Null will not do.

Also, keep in mind that you should run this in each database that you ‘re trying to get this information for.

 

Requirements

Your SQL Server Instance must at least be running v2008.

MS SQL Server v2005 and below

If you happen to be running MS SQL Server v2005 or below, I will suggest that you try this more voluminous path.

Syntax:


select 
	  tblComment.id as objectID
	, tblUser.name as schemaName
	, object_name(tblComment.id) as objectName
	, tblObject.type as [objectType]
	, tblComment.[text] as objectText
	, datalength(tblComment.[text]) as [datalength]
	, tblComment.colid

from   syscomments tblComment

	  inner join sysobjects tblObject
		on tblComment.id = tblObject.id

Sample:

To zero in on specific Linked Server, you can try:

select 
	  tblComment.id as objectID
	, tblUser.name as schemaName
	, object_name(tblComment.id) as objectName
	, tblObject.type as [objectType]
	, tblComment.[text] as objectText
	, datalength(tblComment.[text]) as [datalength]
	, tblComment.colid

from   syscomments tblComment

	  inner join sysobjects tblObject

		on tblComment.id = tblObject.id

where  tblComment.text like '%LS_HRDB%'

The query will look for objects that references LS_HRDB; note that it will also pick up objects that just contain LS_HRDB as part of their comments (/* LSD_HRDB*/).

References

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s