This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Tuesday, September 3, 2013

List All Objects Using Linked Server


While deployment a project on Production server I got an requirement to update my all LINKED Server references which is a very boring  task to me.
I found a very useful function on MSDN to check cross-database dependencies.

/*-----List objects using Linked Server-------*/
SELECT
    Distinct
    referenced_Server_name As LinkedServerName,
    referenced_schema_name AS LinkedServerSchema,
    referenced_database_name AS LinkedServerDB,
    referenced_entity_name As LinkedServerTable,
    OBJECT_NAME (referencing_id) AS ObjectUsingLinkedServer
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
And referenced_Server_name = 'Enter LinkedServerName here'


Thanks

No comments: