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