SQL Server App and DB: How to learn it fast -- not the language, the custom app.

I am a consultant and get handed new projects a lot.  Usually they take the form of my latest, a SQL Server app containing 50,000+ lines of code spread over procs, funx, views etc, in addition to supporting tables PLUS the source and target DBs.

Fortunately the whole thing has been meticulously documented with detailed requirements, run books, comments,schematics, logical and data flow charts, etc.  HAHAHAHAHAHAHA, I mean I got nothing.

So I have done this exercise a few times and am trying make best use of resources.  Assuming I have no 3rd party tools,
what pointers can be offered for familiarizing myself effectively and effeciently with this beast?

My methods to date include:
1) DMVs (my new favorite toys)  Reference and Referencing objects.  I have script that loops through DB and writes what every object touches, so I have a nice table to query.  Not 100% accurate but very handy. I don't look at indexes at this point.

2) SQL Profiler once I have to general map of what touches what I can see what are key proc and funx, then trace them. Using the logs I can see what they call, spawn, etc.

3) Debugger once I have a general sequence down I can walk through a single thread with the debugger.  Bring plenty of snacks.  I narrate the process into my phone then transcribe as well as taking notes.

Any thoughts?  Is there a specific term for this process, more specific than "analysis" or "reverse engineering?"  Getting to the next topic what 3rd party tools are useful?

Thanks,
Hec RamseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
Is there a specific term for this process, more specific than "analysis" or "reverse engineering?
I don't think you can escape including some form of analysis when inheriting any system i.e. application. Therefore, I think terms like the following might be acceptable: Data Model Analysis, and Legacy Database/Application Analysis.

I'm currently working on a Data Warehouse project and I've found the Red Gate Tools SQL Bundle to be very useful. I leverage the Schema & Data Compare for syncing changes between databases. The Documenter tool is great because you can quickly add comments to SQL Server Extended Properties and publish documentation as html, MS Word, or PDF.
https://www.red-gate.com/products/sql-development/sql-developer-suite/

Secondly, the free Oracle Data Modeler Tool has worked well with our 2012 SQL Server Enterprise environment. It even supports "reverse engineering" of existing SQL Databases
http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

Lastly, the following SQL snippet provides a quick summary of the primary and foreign key relationships within a SQL database. I've used this with SQL Server 2008R2 and 2012 all editions:
USE <DATABASE NAME>

SELECT  [Reference Table] = OBJECT_NAME(f.referenced_object_id) ,

        [Reference Table Primary Key] = COL_NAME(fc.referenced_object_id,

                                                 fc.referenced_column_id) ,

        [Target Table] = OBJECT_NAME(f.parent_object_id) ,

        [Foreign Key From Reference Table] = COL_NAME(fc.parent_object_id,

                                                      fc.parent_column_id) ,

        [Target Table Foreign Key Constraint] = f.name

FROM    sys.foreign_keys AS f

        INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

ORDER BY [Reference Table] ,

        [Target Table] ASC

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.