SQL Server Object dependencies

[Modesto Vega]

We just got a database reversed engineered but got an unexpected result. Somehow, I was expecting Sparx EA to import the same dependencies between different stored procedures and between stored procedures and tables we can see using SQL Server Management Studio.

Sparx EA has not imported any stored procedure dependencies, have we missed something?

[Geert Bellekens]

I'ts been a long time since I reverse engineered a database with stored procedures, but your results align with my expectations.

In order to detect dependencies between stored procedures/table, the code will have to be parsed. I can imagine that information is not included in the ODBC driver.

Geert

[Modesto Vega]

Thank you Geert. The strange thing is that it identifies dependencies between tables and views and I think it may have also identified dependencies between stored procedures and tables but I need to double check the later.

[Paolo F Cantoni]

Thank you, Geert.  The strange thing is that it identifies dependencies between tables and views, and I think it may have also identified dependencies between stored procedures and tables, but I need to double-check the latter.

Hi Modest, a partially developed SQL parser seems to be in the Database builder.  Over a decade ago, I worked for an organisation with NO idea where their data was - nearly 100 databases spread over multiple servers.  We used a commercially available SQL parser to build an add-in that would REALLY parse the reverse-engineered DB code from the appropriate items and created the required links to the referenced items.

It could do some really amazing things like cross-server references, identification of CTEs in different DBs, etc.  The large number of DBs to be processed and the lack of knowledge and, thus, the value of the insights gained made it worthwhile.  Unfortunately, it is currently non-operational.  Sparx EA has moved on, SQL has moved on, the Parser has moved on...

Not sure if that helps, but the results were great.  It may be an option for you.

Paolo

[Modesto Vega]

Thank you Paulo, the Database builder parser does, at best, half the job. I am still convinced that, in previous versions, it did a better job, at least more than half. But I don't have the time to look into it/prove it.

[SNIP]
In order to detect dependencies between stored procedures/table, the code will have to be parsed. I can imagine that information is not included in the ODBC driver.

Forgot to mention, that the dependencies are stored in the database, specifically in very readable system tables, there is no need to parse the code; at least not for we are after, it should be more a question of having the component of Sparx EA that reverse engineers databases call the right system functions/stored procedures to extract the dependencies. I guess that we can extract them and import them manually but it is an inefficient use of time and resources.

[Geert Bellekens]

Thank you Paulo, the Database builder parser does, at best, half the job. I am still convinced that, in previous versions, it did a better job, at least more than half. But I don't have the time to look into it/prove it.

[SNIP]
In order to detect dependencies between stored procedures/table, the code will have to be parsed. I can imagine that information is not included in the ODBC driver.

Forgot to mention, that the dependencies are stored in the database, specifically in very readable system tables, there is no need to parse the code; at least not for we are after, it should be more a question of having the component of Sparx EA that reverse engineers databases call the right system functions/stored procedures to extract the dependencies. I guess that we can extract them and import them manually but it is an inefficient use of time and resources.

That might be, but I don't think that's how this reverse engineering works in EA. AFAIK they are using the ODBC connection to read the metadata, not the system tables in SQL Server.
That would be how I would implement it since ODBC does all the translation from the different databases to a standard metamodel.

Geert

[Modesto Vega]

For SQL Server all they need to do is called 2 system functions: sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities. Something similar applies to other enterprise level RDBMS. If they are being called, some results are being ignored.