<p>We're having problem with a huge number of legacy stored procedures at work. Do you guys recommend any tool the can help better understand those procedures? Some kind of reverse engineering that indentifies inter-procedure dependencies and/or procedure vs. tables dependencies. Can be a free or commercial tool.</p>

<p>Thanks!</p>

[{'answer_id': 69953, 'author': 'rpetrich', 'author_id': 4007, 'author_profile': 'https://Stackoverflow.com/users/4007', 'pm_score': 3, 'selected': True, 'text': '<p>Redgate has a rather expensive product called <a href="http://www.red-gate.com/products/SQL_Dependency_Tracker/" rel="nofollow noreferrer">SQL Dependency Tracker</a> that seems to fulfill the requirements.</p>\n'}, {'answer_id': 69980, 'author': 'Tyler', 'author_id': 5642, 'author_profile': 'https://Stackoverflow.com/users/5642', 'pm_score': 2, 'selected': False, 'text': '<p>I think the <a href="https://stackoverflow.com/questions/69923/stored-procedures-reverse-engineering#69953">Red Gate Dependency Tracker mentioned by rpetrich</a> is a decent solution, it works well and Red Gate has 30 day trial (ideally long enough for you do do your forensics).</p>\n\n<p>I would also consider isolating the system and running the <strong>SQL Profiler which will show you all the SQL action on the tables</strong>. This is often a <strong>good starting point for building a sequence diagram or however you choose to document these codes</strong>. Good luck!  </p>\n'}, {'answer_id': 79428, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>Redgate SQL Doc. the generated documentation included cross-referenced dependency information. For example, for each table, it lists views, stored procedures, triggers etc that reference that table.  </p>\n'}, {'answer_id': 86288, 'author': 'Mike McAllister', 'author_id': 16247, 'author_profile': 'https://Stackoverflow.com/users/16247', 'pm_score': 1, 'selected': False, 'text': '<p>What database are the stored procedures in? Oracle, SQL Server, something else?</p>\n\n<p><strong>Edit based on comment:</strong> Given you\'re using Oracle then, have a look at <a href="http://www.quest.com/toad-for-oracle/" rel="nofollow noreferrer">TOAD</a>. I use a feature in it called the Code Roadmap, which allows you to graphically display PL/SQL interdependancies within the database. It can run in Code Only mode, showing runtime call stack dependancies, or Code Plus Data mode, where it also shows you database objects (tables, views, triggers) that are touched by your code.</p>\n\n<p><em>(Note - I am a TOAD user, and gain no benefit from referring it)</em></p>\n'}, {'answer_id': 93967, 'author': 'ConcernedOfTunbridgeWells', 'author_id': 15401, 'author_profile': 'https://Stackoverflow.com/users/15401', 'pm_score': 3, 'selected': False, 'text': "<p>The cheaper solution than 'dependency tracker' is the data dictionary table sys.sql_dependencies which from which this data can be queried from the data dictionary.  Oracle has a data dictionary view with similar functionality called DBA_DEPENDENCIES (plus equivalent USER_ and ALL_ views) .  Using the other data dictionary tables (sys.tables/DBA_TABLES) etc. you can generate object dependency reports.</p>\n\n<p>If you're feeling particularly keen you can use a recursive query (Oracle CONNECT BY or SQL Server Common Table Expressions) to build a complete object dependency graph.</p>\n\n<p>Here's an example of a recursive CTE on sys.sql_dependencies.  It will return an entry for every dependency with its depth.  Items can occur more than once, possibly at different depths, for every dependency relationship.  I don't have a working Oracle instance to hand to build a CONNECT BY query on DBA_DEPENDENCIES so anyone with edit privileges and the time and expertise is welcome to annotate or edit this answer.</p>\n\n<p>Note also with <code>sys.sql_dependencies</code> that you can get column references from <code>referenced_minor_id</code>.  This could be used (for example) to determine which columns were actually used in the ETL sprocs from a staging area with copies of the DB tables from the source with more columns than are actually used.</p>\n\n<pre><code>with dep_cte as (\nselect o2.object_id  as parent_id\n      ,o2.name       as parent_name\n      ,o1.object_id  as child_id\n      ,o1.name       as child_name\n      ,d.referenced_minor_id\n      ,1 as hierarchy_level\n  from sys.sql_dependencies d\n  join sys.objects o1\n    on o1.object_id = d.referenced_major_id\n  join sys.objects o2\n    on o2.object_id = d.object_id\n where d.referenced_minor_id in (0,1)\n   and not exists\n       (select 1\n          from sys.sql_dependencies d2\n         where d2.referenced_major_id = d.object_id)\n\nunion all\n\nselect o2.object_id  as parent_id\n      ,o2.name       as parent_name\n      ,o1.object_id  as child_id\n      ,o1.name       as child_name\n      ,d.referenced_minor_id\n      ,d2.hierarchy_level + 1 as hierarchy_level\n  from sys.sql_dependencies d\n  join sys.objects o1\n    on o1.object_id = d.referenced_major_id\n  join sys.objects o2\n    on o2.object_id = d.object_id\n  join dep_cte d2\n    on d.object_id = d2.child_id\n where d.referenced_minor_id in (0,1)\n)\n\nselect *\n  from dep_cte\n order by hierarchy_level\n</code></pre>\n\n<p>I've got this to open-up to the community now.  Could someone with convenient access to a running Oracle instance post a CONNECT BY recursive query here?  Note that this is SQL-server specific and the question owner has since made it clear that he's using Oracle.  I don't have a running Oracle instance to hand to develop and test anything.</p>\n"}, {'answer_id': 94484, 'author': 'jkinter', 'author_id': 10405, 'author_profile': 'https://Stackoverflow.com/users/10405', 'pm_score': 1, 'selected': False, 'text': "<p>This isn't real deep or thorough, but I think that if you're using MS SQL Server or Oracle (Perhaps Nigel can help with a PL-SQL sample)...Nigel is on to something . This only goes 3 dependencies deep, but could be modified to go however deep you need. It's not the prettiest thing...but it's functional...</p>\n\n<pre><code>select \n    so.name + case when so.xtype='P' then ' (Stored Proc)' when so.xtype='U' then ' (Table)' when so.xtype='V' then ' (View)' else ' (Unknown)' end as EntityName, \n    so2.name + case when so2.xtype='P' then ' (Stored Proc)' when so2.xtype='U' then ' (Table)' when so2.xtype='V' then ' (View)' else ' (Unknown)' end as FirstDependancy,\n    so3.name + case when so3.xtype='P' then ' (Stored Proc)' when so3.xtype='U' then ' (Table)' when so3.xtype='V' then ' (View)' else ' (Unknown)' end as SecondDependancy,\n    so4.name + case when so4.xtype='P' then ' (Stored Proc)' when so4.xtype='U' then ' (Table)' when so4.xtype='V' then ' (View)' else ' (Unknown)' end as ThirdDependancy\nfrom \n  sysdepends sd \n    inner join sysobjects as so on sd.id=so.id \n    left join sysobjects as so2 on sd.depid=so2.id\n    left join sysdepends as sd2 on so2.id=sd2.id and so2.xtype not in ('S','PK','D')\n    left join sysobjects as so3 on sd2.depid=so3.id and so3.xtype not in ('S','PK','D')\n    left join sysdepends as sd3 on so3.id=sd3.id and so3.xtype not in ('S','PK','D')\n    left join sysobjects as so4 on sd3.depid=so4.id and so4.xtype not in ('S','PK','D')\nwhere so.xtype = 'P' and left(so.name,2)&lt;&gt;'dt'\ngroup by so.name, so2.name, so3.name, so4.name, so.xtype, so2.xtype, so3.xtype, so4.xtype\n</code></pre>\n"}, {'answer_id': 96629, 'author': 'Thomas Wagner', 'author_id': 13997, 'author_profile': 'https://Stackoverflow.com/users/13997', 'pm_score': 0, 'selected': False, 'text': '<p>The single best tool for reverse engineering is by APEX. Its amazing. It can even trace into .NET assemblies and tell you where the procs are used. Its by far the deepest product of its kind. RedGate has great other tools but not in this case. </p>\n'}, {'answer_id': 7923236, 'author': 'user423430', 'author_id': 423430, 'author_profile': 'https://Stackoverflow.com/users/423430', 'pm_score': 1, 'selected': False, 'text': '<p><a href="http://beyondrelational.com/blogs/jacob/archive/2008/09/18/tsql-lab-9-how-to-find-the-dependency-chain-of-a-database-object.aspx" rel="nofollow">How to find the dependency chain of a database object</a> (MS SQL Server 2000(?)+)\nby Jacob Sebastian</p>\n\n<blockquote>\n  <p>Every time he needs to deploy a new report or modify an existing\n  report, he needs to know what are the database objects that depend on\n  the given report stored procedure. Some times the reports are very\n  complex and each stored procedure might have dozens of dependent\n  objects and each dependent object may be depending on other dozens of\n  objects.</p>\n  \n  <p>He needed a way to recursively find all the depending objects of a\n  given stored procedure. I wrote a recursive query using CTE to achieve\n  this.</p>\n</blockquote>\n'}]
['https://Stackoverflow.com/questions/69923', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4213/']