Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Get list of tables used in a stored procedure Expand / Collapse
Author
Message
Posted Tuesday, February 16, 2010 11:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:45 PM
Points: 110, Visits: 152
This is really a nice piece of information. I was just surfing the net and got this. It really made my life easy though I have to add some code to match my use case.
I too got the result after commenting the depnumber=1
Post #866772
Posted Tuesday, April 19, 2011 9:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:17 AM
Points: 2,434, Visits: 7,513
I realise this is old, but I found it useful today.

I modified the code: -

;WITH stored_procedures AS (
SELECT
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

This gets rid of the issue that some were having with duplicated table names.

Also - please bear in mind that "o.xtype = 'P'" means I am only returning stored-procedures, nothing else.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1095718
Posted Thursday, June 30, 2011 1:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:04 AM
Points: 1, Visits: 79
Greate job :):)
Post #1134215
Posted Friday, October 14, 2011 4:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 4:22 AM
Points: 1, Visits: 48
thanks..its save lots of time..
Post #1190422
Posted Wednesday, October 26, 2011 6:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 3:45 AM
Points: 12, Visits: 30
Great job. Thank you very much. It saved lot of time. :)
Post #1196708
Posted Sunday, February 10, 2013 2:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 10, 2013 2:07 PM
Points: 1, Visits: 0
Thank you!
Post #1418137
Posted Friday, May 16, 2014 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 2:05 PM
Points: 1, Visits: 2
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
Post #1571773
Posted Friday, May 16, 2014 10:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:32 PM
Points: 13,302, Visits: 12,168
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_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 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1571815
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse