Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Show all Table References (FROMs and JOINs)

Author  Topic 

thomasfischer25
Starting Member

3 Posts

Posted - 2013-05-31 : 08:54:24
Hey guys,

As the Subject already says, I want to list all the dependencies of Views/Stored Procedures/Functions.

For example:

View ABC looks like that

SELECT *
FROM TableA
INNER JOIN TableB
ON TableB.Id = TableA.Id


Then I would like to have a result like

Type Name Reference
-------------------------
View ABC TableA
View ABC TableB
.
.
.

Is there a SQL Command to get something like that?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-31 : 11:04:59
You should be able to use sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. There are some examples here: http://msdn.microsoft.com/en-us/library/bb677168(v=sql.105).aspx
Go to Top of Page

thomasfischer25
Starting Member

3 Posts

Posted - 2013-05-31 : 14:36:20
Thanks thats great!

I used following code:



SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name
,referenced_server_name AS server_name
,referenced_database_name AS database_name
,referenced_schema_name AS schema_name
, referenced_entity_name
,*
FROM sys.sql_expression_dependencies



But how can I get the Type of each element due to its reference id?

The Table I get back after executing posted code will include
referenced_id
and
referencing_id

Can I get the Type out of the id?
With type I mean:
- Table
- View
- Stored Procedure
- Function
Go to Top of Page

thomasfischer25
Starting Member

3 Posts

Posted - 2013-05-31 : 14:58:25
Found a way!

Thanks guys...

Here is my solution: (Quick & Dirty)



SELECT
Referencing = CASE WHEN OBJECTPROPERTY(referencing_id,'IsTable') = 1 THEN 'Table'
WHEN OBJECTPROPERTY(referencing_id,'IsView') = 1 THEN 'View'
WHEN OBJECTPROPERTY(referencing_id,'IsProcedure') = 1 THEN 'Procedure'
WHEN OBJECTPROPERTY(referencing_id,'IsTableFunction') = 1 THEN 'TableFunction'
WHEN OBJECTPROPERTY(referencing_id,'IsScalarFunction') = 1 THEN 'ScalarFunction'
ELSE NULL
END

,OBJECT_NAME(referencing_id) AS referencing_entity_name
,referenced_server_name AS server_name
,referenced_database_name AS database_name
,referenced_schema_name AS schema_name

,Referenced = CASE WHEN OBJECTPROPERTY(referenced_id,'IsTable') = 1 THEN 'Table'
WHEN OBJECTPROPERTY(referenced_id,'IsView') = 1 THEN 'View'
WHEN OBJECTPROPERTY(referenced_id,'IsProcedure') = 1 THEN 'Procedure'
WHEN OBJECTPROPERTY(referenced_id,'IsTableFunction') = 1 THEN 'TableFunction'
WHEN OBJECTPROPERTY(referenced_id,'IsScalarFunction') = 1 THEN 'ScalarFunction'
ELSE NULL
END
,referenced_entity_name
--,*
FROM sys.sql_expression_dependencies

Go to Top of Page
   

- Advertisement -