SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Show all Table References (FROMs and JOINs)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thomasfischer25
Starting Member

3 Posts

Posted - 05/31/2013 :  08:54:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 05/31/2013 :  11:04:59  Show Profile  Reply with Quote
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 - 05/31/2013 :  14:36:20  Show Profile  Reply with Quote
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 - 05/31/2013 :  14:58:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000