Get list of tables used in a stored procedure

  • Deepthi Viswanathan Nair

    Old Hand

    Points: 392

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


    Kindest Regards,

    Deepthi Viswanathan Nair

  • neela_kantam80

    SSC-Addicted

    Points: 427

    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

  • naveen_yashman-703166

    SSC Enthusiast

    Points: 125

    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.

  • neela_kantam80

    SSC-Addicted

    Points: 427

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

  • neela_kantam80

    SSC-Addicted

    Points: 427

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

  • Jerry Hung

    SSChampion

    Points: 12858

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

    SSC-Addicted

    Points: 427

    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

  • neela_kantam80

    SSC-Addicted

    Points: 427

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

  • plucki duck

    SSC Journeyman

    Points: 88

    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

  • ameya.2709

    SSC Journeyman

    Points: 85

    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

  • dreamthebest

    Ten Centuries

    Points: 1264

    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

  • Cadavre

    SSC-Forever

    Points: 41582

    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/

  • Elango-740197

    SSC Journeyman

    Points: 83

    Greate job :):)

  • londheapil

    Valued Member

    Points: 61

    thanks..its save lots of time..

  • s.gade

    SSC Enthusiast

    Points: 104

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

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

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