February 10, 2013 at 2:07 pm
Thank you!
May 16, 2014 at 8:55 am
This answer uses a number of deprecated tables that will no longer be supported.
Here's a simpler way to achieve the same thing:
SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables t ON t.object_id = d.referenced_major_id
ORDER BY proc_name,table_name
For more info, you can see my answer on stack overflow about this issue.
Update: Edited Ordinal Order By per Sean's suggestion
May 16, 2014 at 10:29 am
kylemit (5/16/2014)
This answer uses a number of deprecated tables that will no longer be supported.Here's a simpler way to achieve the same thing:
SELECT DISTINCT p.name AS proc_name, t.name AS table_nameFROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables t ON t.object_id = d.referenced_major_id
ORDER BY 1,2
For more info, you can see my answer on stack overflow about this issue.
Resurrected a pretty old thread here. 😉 I like your solution but you really should not use ordinal position in your ORDER BY.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 3, 2014 at 11:57 pm
Hey Jerry Hung,
I have tried the function to get list of table names used in a stored procedure and it works great.. Now I want to get row count for each table used in the stored procedure. How can I do this?
March 2, 2015 at 1:55 pm
Your modified version is actually better....It even picks up Views!
October 5, 2016 at 12:47 am
I have executed the above script . But some tables are missing .
October 5, 2016 at 8:59 am
You do realize that this thread is so old that the original article was likely written for SQL 2005, which is now deprecated. None of the scripts mention which version they were written for. You also don't mention which version of SQL Server you are using.
You should consider using sys.dm_sql_referenced_entities.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 5, 2016 at 9:51 am
Like today, within SSMS Object Explorer, you can right click on a stored procedure, table, view, etc. and select 'View Dependencies'.
Still there are occasions where it's useful to query the underlying system views directly. For example, you may want to leverage sp_MsForEachDB and sp_MsForEachTable to quickly identify every object globally that references a specific table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply