question

Cyborg avatar image
Cyborg asked

sys.dm_sql_referencing_entities...

I was in task of cleaning-up unused columns in a table, I am using the DMV sys.dm_sql_referencing_entity and sys.dm_sql_referenced_entity to find the depended object. But these DMVs are not listing all the depended object. For example, consider the following object in which i need to find depended objects on column "Col1" in Users Table -

CREATE  TABLE DependencyTest
(ID INT,
 Col1 VARCHAR(10)
 
 )
 GO
 
 CREATE PROCEDURE usp_test
 AS
 BEGIN
		
		CREATE TABLE #temp(tID INT, tCol1 VARCHAR(10))
		
		INSERT INTO #temp(tID, tCol1)
		SELECT ID, Col1
		FROM DependencyTest
		
		DROP TABLE #temp
 
 END
 GO
 
 SELECT referenced_minor_name,referencing_schema_name+'.'+referencing_entity_name
 FROM sys.dm_sql_referencing_entities('dbo.DependencyTest', 'object')
 CROSS APPLY sys.dm_sql_referenced_entities(referencing_schema_name+'.'+referencing_entity_name,'object')
 WHERE referenced_minor_name IN ('Col1')
If i run the DMV query, usp_test is not listed in the result-set even though the columns are referenced inside this SP. Any explanation about this behavior?
sql-server-2008dependency
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
My guess would be that because dependency info for temporary tables is an issue from [BOL][1] > Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects. the `SELECT` that is part of the `INSERT INTO` gets ignored. [1]: http://msdn.microsoft.com/en-us/library/bb677185.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The efficient way is to do what you're doing. You're looking for both an accurate and efficient way. There isn't any way to do both.
1 Like 1 ·
+1, So, is there an efficient way to track the dependency objects?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
This is a weak answer for how to track dependencies, but since so much about dependencies is subject to interpretation and SQL Server can do a pretty cruddy job, instead of looking for direct dependencies, I'd suggest looking for references. Anywhere a column or table is referenced, treat it as if it were a dependency. Not a good solution, but it's somewhat workable. For easy searches on references, I'd suggest using Red Gate SQL Search. [A 100% free tool][1]. [1]: http://www.red-gate.com/products/sql-development/sql-search/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is there a way to ignore search on commented lines in Redgate SQL Search?
0 Likes 0 ·
Not to my knowledge. It's a pretty simple tool.
0 Likes 0 ·
You are right it searches commented Lines as well, I installed just now. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.