July 20, 2015

Search All objects that reference a table or object

Lately I have been searching and analyzing many of our systems, so, as been a lazy programmer here is a script to search any object within sql sever 2008 and above and get all the references that are using that table, procedure, view etc...

Here it is...
use AdventureWorks2012
go
set transaction isolation level read uncommitted;
set nocount on;

declare @TableList table (ObjectID bigint,Table_Schema varchar(50),Table_Name varchar(80))

insert into @TableList(objectid,Table_Schema,Table_Name)
select
 object_id(Table_Schema+'.'+Table_name),TABLE_SCHEMA,TABLE_NAME
from INFORMATION_SCHEMA.tables where TABLE_SCHEMA = 'Person'

SELECT 
    OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name
    ,OBJECT_NAME(referencing_id) AS referencing_entity_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
,object_NAME (parent_object_id) as Parent_Name
    ,COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name
    ,is_caller_dependent
,is_ambiguous
FROM 
sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o 
ON sed.referencing_id = o.object_id
INNER JOIN @TableList tcs 
ON ((referenced_id = tcs.objectid) or (parent_object_id = tcs.ObjectID))
order by 3 desc

No comments:

Post a Comment

Contact Form

Name

Email *

Message *