August 13, 2015

Find Object reference across Database

Don't you always want to know where a table or database base object is been used... Well here it is... enjoy..
Use DatabasNameHere;
go
Set transaction isolation level read uncommitted;
set nocount on;

declare @TableName varchar(80) = 'TableNameGoesHere'
declare @TableSchema varchar(50) = 'TableSchemaGoesHere'
declare @objectid bigint = object_id(@TableSchema+'.'+@TableName);


;with cte_refe as
(
SELECT 
    OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name, 
object_name(o.Parent_object_id) as Parent_Name,
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, 
is_ambiguous,
is_replicated,
o.is_published,
m.definition

FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
inner join sys.tables t on t.object_id = @objectid
left outer join sys.all_sql_modules m
on (m.object_id = sed.referencing_id) 

WHERE  ((@objectid = referenced_id) or (@objectid = o.parent_object_id))
--order by 3,1,2
)
select distinct 
       referencing_desciption
  ,Parent_Name
 ,isnull (referencing_schema_name,'dbo')+'.'+referencing_entity_name referencing_entity_name
 ,isnull (referenced_schema_name,'dbo')+'.'+referenced_entity_name As TableName
-- ,referenced_server_name
 ,@@SERVERNAME ServerName
,is_caller_dependent 
  ,is_ambiguous
,is_replicated
,is_published
,definition
 
from
cte_refe
where referencing_entity_name  not like '%syncobj_0%'
--where referenced_server_name is not null
order by 1 desc,2

No comments:

Post a Comment

Contact Form

Name

Email *

Message *