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 Sunday, September 23, 2007 10:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 12:49 PM
Points: 8, Visits: 25
Comments posted to this topic are about the item Get list of tables used in a stored procedure


Kindest Regards,

Deepthi Viswanathan Nair

Post #401606
Posted Wednesday, October 29, 2008 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2008 2:46 AM
Points: 5, Visits: 9
i am unable to get the name of tables if the storedprocedure is very long and storedprocedures using the dynamic querys can u help in this regards
Post #593659
Posted Wednesday, October 29, 2008 12:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 5:47 PM
Points: 7, Visits: 19
Please note just creating the stored procedure will not bring the table names being used within the stored procedure.
To get the list of table names being used with the above said logic, the stored procedure should have been executed at least once. For e.g Exec your_sp_name parameters.
Post #593869
Posted Wednesday, October 29, 2008 11:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2008 2:46 AM
Points: 5, Visits: 9
i have executed the sp but still i am unable to get the table names
Post #594089
Posted Wednesday, October 29, 2008 11:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2008 2:46 AM
Points: 5, Visits: 9
i got the solution by removing depnumber=1 i am able to get the details
Post #594093
Posted Thursday, October 30, 2008 8:08 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 772, Visits: 1,183
depnumber=1 seems to limit the outputs
but I think the purpose was to only show UNIQUE tables (example below should only show BillOfMaterials and Product)

In AdventureWorks2008 (on SQL2008)
SELECT DISTINCT
o.id, o.name AS 'Procedure_Name' , oo.name AS 'Table_Name', d.depid
--, d.depnumber -- comment this out returns unique tables only
FROM sysdepends d, sysobjects o, sysobjects oo
WHERE o.id=d.id
AND o.name= 'uspGetBillOfMaterials' -- Stored Procedure Name
AND oo.id=d.depid
--and depnumber=1
ORDER BY o.name,oo.name

returns

id Procedure_Name Table_Name depid depnumber
2123154609 uspGetBillOfMaterials BillOfMaterials 213575799 2
2123154609 uspGetBillOfMaterials BillOfMaterials 213575799 3
2123154609 uspGetBillOfMaterials BillOfMaterials 213575799 4
2123154609 uspGetBillOfMaterials BillOfMaterials 213575799 5
2123154609 uspGetBillOfMaterials BillOfMaterials 213575799 7
2123154609 uspGetBillOfMaterials BillOfMaterials 213575799 8
2123154609 uspGetBillOfMaterials Product 1717581157 1
2123154609 uspGetBillOfMaterials Product 1717581157 2
2123154609 uspGetBillOfMaterials Product 1717581157 9
2123154609 uspGetBillOfMaterials Product 1717581157 10


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #594351
Posted Thursday, October 30, 2008 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2008 2:46 AM
Points: 5, Visits: 9
i have found that the storedprocedure id is missing in sysdepends table
but the storedprocedured is present in the database .

can any one tell me why the id's of some of the storedprocedures are not turning up in sysdepends table







Post #594376
Posted Thursday, October 30, 2008 11:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2008 2:46 AM
Points: 5, Visits: 9
storedprocedure id is present in sysobjects table but it is not there in sysdepends table can u let me know why this discrepancy
Post #594777
Posted Monday, November 17, 2008 9:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 5:03 PM
Points: 6, Visits: 293
I don't seem to get any tables back if the stored procedure references linked server tables. Is there a way to remedy this?

thanks
Post #603775
Posted Monday, July 6, 2009 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 15, 2011 12:26 AM
Points: 1, Visits: 18
hey

i have got the list of procedures, but din't get all the SP's.
also i have refreshed the DB an d checked tht. but still some of the SP's are missing from the list
Post #747659
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse