Post reply

Get list of tables used in a stored procedure

  • September 23, 2007 at 10:55 am

    #177917

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

    Kindest Regards,

    Deepthi Viswanathan Nair

  • October 29, 2008 at 8:39 am

    #891256

    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

  • October 29, 2008 at 12:48 pm

    #891428

    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.

  • October 29, 2008 at 11:07 pm

    #891605

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

  • October 29, 2008 at 11:24 pm

    #891609

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

  • October 30, 2008 at 8:08 am

    #891816

    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

    idProcedure_NameTable_Namedepiddepnumber

    2123154609uspGetBillOfMaterialsBillOfMaterials2135757992

    2123154609uspGetBillOfMaterialsBillOfMaterials2135757993

    2123154609uspGetBillOfMaterialsBillOfMaterials2135757994

    2123154609uspGetBillOfMaterialsBillOfMaterials2135757995

    2123154609uspGetBillOfMaterialsBillOfMaterials2135757997

    2123154609uspGetBillOfMaterialsBillOfMaterials2135757998

    2123154609uspGetBillOfMaterialsProduct17175811571

    2123154609uspGetBillOfMaterialsProduct17175811572

    2123154609uspGetBillOfMaterialsProduct17175811579

    2123154609uspGetBillOfMaterialsProduct171758115710

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • October 30, 2008 at 8:30 am

    #891840

    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

  • October 30, 2008 at 11:04 pm

    #892164

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

  • November 17, 2008 at 9:49 am

    #899415

    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

  • July 6, 2009 at 6:37 am

    #1020317

    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

  • February 16, 2010 at 11:42 pm

    #1119862

    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

  • April 19, 2011 at 9:02 am

    #1313426

    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.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    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/

  • June 30, 2011 at 1:06 am

    #1346327

    Greate job :):)

  • October 14, 2011 at 4:45 am

    #1394541

    thanks..its save lots of time..

  • October 26, 2011 at 6:39 pm

    #1399967

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

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply