Show all Table References (FROMs and JOINs)

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources