SQLServerCentral / Discuss content posted by Deepthi Viswanathan Nair / Article Discussions / Article Discussions by Author / Get list of tables used in a stored procedure / Latest Posts

RE: Get list of tables used in a stored procedure

joaoribeiroaraujo 3190 — Sun, 10 Feb 2013 14:07:56 GMT

Thank you!

RE: Get list of tables used in a stored procedure

s.gade — Wed, 26 Oct 2011 18:39:27 GMT

Great job. Thank you very much. It saved lot of time. :)

RE: Get list of tables used in a stored procedure

londheapil — Fri, 14 Oct 2011 04:45:49 GMT

thanks..its save lots of time..

RE: Get list of tables used in a stored procedure

Elango-740197 — Thu, 30 Jun 2011 01:06:54 GMT

Greate job :):)

RE: Get list of tables used in a stored procedure

Cadavre — Tue, 19 Apr 2011 09:02:27 GMT

I realise this is old, but I found it useful today.I modified the code: -[code="sql"];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 rowFROM sysdepends d INNER JOIN sysobjects o ON o.id=d.idINNER JOIN sysobjects oo ON oo.id=d.depidWHERE o.xtype = 'P')SELECT proc_name, table_name FROM stored_proceduresWHERE row = 1ORDER BY proc_name,table_name[/code]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.

RE: Get list of tables used in a stored procedure

dreamthebest — Tue, 16 Feb 2010 23:42:58 GMT

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

RE: Get list of tables used in a stored procedure

ameya.2709 — Mon, 06 Jul 2009 06:37:23 GMT

heyi 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

RE: Get list of tables used in a stored procedure

plucki duck — Mon, 17 Nov 2008 09:49:01 GMT

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

RE: Get list of tables used in a stored procedure

neela_kantam80 — Thu, 30 Oct 2008 23:04:49 GMT

storedprocedure id is present in sysobjects table but it is not there in sysdepends table can u let me know why this discrepancy

RE: Get list of tables used in a stored procedure

neela_kantam80 — Thu, 30 Oct 2008 08:30:42 GMT

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

RE: Get list of tables used in a stored procedure

Jerry Hung — Thu, 30 Oct 2008 08:08:40 GMT

depnumber=1 seems to limit the outputsbut I think the purpose was to only show UNIQUE tables (example below should only show [u]BillOfMaterials[/u] and [u]Product[/u])In AdventureWorks2008 (on SQL2008)[code]SELECT DISTINCT o.id, o.name AS 'Procedure_Name' , oo.name AS 'Table_Name', d.depid--, d.depnumber -- comment this out returns unique tables onlyFROM sysdepends d, sysobjects o, sysobjects ooWHERE o.id=d.id AND o.name= 'uspGetBillOfMaterials' -- Stored Procedure Name AND oo.id=d.depid --and depnumber=1ORDER BY o.name,oo.name[/code]returns[quote]id Procedure_Name Table_Name depid depnumber2123154609 uspGetBillOfMaterials BillOfMaterials 213575799 22123154609 uspGetBillOfMaterials BillOfMaterials 213575799 32123154609 uspGetBillOfMaterials BillOfMaterials 213575799 42123154609 uspGetBillOfMaterials BillOfMaterials 213575799 52123154609 uspGetBillOfMaterials BillOfMaterials 213575799 72123154609 uspGetBillOfMaterials BillOfMaterials 213575799 82123154609 uspGetBillOfMaterials Product 1717581157 12123154609 uspGetBillOfMaterials Product 1717581157 22123154609 uspGetBillOfMaterials Product 1717581157 92123154609 uspGetBillOfMaterials Product 1717581157 10[/quote]

RE: Get list of tables used in a stored procedure

neela_kantam80 — Wed, 29 Oct 2008 23:24:38 GMT

i got the solution by removing depnumber=1 i am able to get the details

RE: Get list of tables used in a stored procedure

neela_kantam80 — Wed, 29 Oct 2008 23:07:02 GMT

i have executed the sp but still i am unable to get the table names

RE: Get list of tables used in a stored procedure

naveen_yashman-703166 — Wed, 29 Oct 2008 12:48:30 GMT

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 [b]at least [/b]once. For e.g Exec your_sp_name parameters.

RE: Get list of tables used in a stored procedure

neela_kantam80 — Wed, 29 Oct 2008 08:39:07 GMT

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

Get list of tables used in a stored procedure

Deepthi Viswanathan Nair — Sun, 23 Sep 2007 10:55:52 GMT

Comments posted to this topic are about the item [B]Get list of tables used in a stored procedure[/B]