Get list of tables used in a stored procedure

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


    Kindest Regards,

    Deepthi Viswanathan Nair

  • 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

  • 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.

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

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

  • 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
  • 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

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

  • 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

  • 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

  • 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

  • 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/

  • Greate job :):)

  • thanks..its save lots of time..

  • 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