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: Today @ 4:55 AM
Points: 2,403, Visits: 7,310
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 08, 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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse