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